1"""
   2## Expressions
   3
   4Every AST node in SQLGlot is represented by a subclass of `Expression`.
   5
   6This module contains the implementation of all supported `Expression` types. Additionally,
   7it exposes a number of helper functions, which are mainly used to programmatically build
   8SQL expressions, such as `sqlglot.expressions.select`.
   9
  10----
  11"""
  12
  13from __future__ import annotations
  14
  15import datetime
  16import math
  17import numbers
  18import re
  19import textwrap
  20import typing as t
  21from collections import deque
  22from copy import deepcopy
  23from enum import auto
  24from functools import reduce
  25
  26from sqlglot.errors import ErrorLevel, ParseError
  27from sqlglot.helper import (
  28    AutoName,
  29    camel_to_snake_case,
  30    ensure_collection,
  31    ensure_list,
  32    is_int,
  33    seq_get,
  34    subclasses,
  35)
  36from sqlglot.tokens import Token
  37
  38if t.TYPE_CHECKING:
  39    from sqlglot._typing import E, Lit
  40    from sqlglot.dialects.dialect import DialectType
  41
  42
  43class _Expression(type):
  44    def __new__(cls, clsname, bases, attrs):
  45        klass = super().__new__(cls, clsname, bases, attrs)
  46
  47        # When an Expression class is created, its key is automatically set to be
  48        # the lowercase version of the class' name.
  49        klass.key = clsname.lower()
  50
  51        # This is so that docstrings are not inherited in pdoc
  52        klass.__doc__ = klass.__doc__ or ""
  53
  54        return klass
  55
  56
  57SQLGLOT_META = "sqlglot.meta"
  58TABLE_PARTS = ("this", "db", "catalog")
  59
  60
  61class Expression(metaclass=_Expression):
  62    """
  63    The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary
  64    context, such as its child expressions, their names (arg keys), and whether a given child expression
  65    is optional or not.
  66
  67    Attributes:
  68        key: a unique key for each class in the Expression hierarchy. This is useful for hashing
  69            and representing expressions as strings.
  70        arg_types: determines the arguments (child nodes) supported by an expression. It maps
  71            arg keys to booleans that indicate whether the corresponding args are optional.
  72        parent: a reference to the parent expression (or None, in case of root expressions).
  73        arg_key: the arg key an expression is associated with, i.e. the name its parent expression
  74            uses to refer to it.
  75        comments: a list of comments that are associated with a given expression. This is used in
  76            order to preserve comments when transpiling SQL code.
  77        type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the
  78            optimizer, in order to enable some transformations that require type information.
  79        meta: a dictionary that can be used to store useful metadata for a given expression.
  80
  81    Example:
  82        >>> class Foo(Expression):
  83        ...     arg_types = {"this": True, "expression": False}
  84
  85        The above definition informs us that Foo is an Expression that requires an argument called
  86        "this" and may also optionally receive an argument called "expression".
  87
  88    Args:
  89        args: a mapping used for retrieving the arguments of an expression, given their arg keys.
  90    """
  91
  92    key = "expression"
  93    arg_types = {"this": True}
  94    __slots__ = ("args", "parent", "arg_key", "comments", "_type", "_meta", "_hash")
  95
  96    def __init__(self, **args: t.Any):
  97        self.args: t.Dict[str, t.Any] = args
  98        self.parent: t.Optional[Expression] = None
  99        self.arg_key: t.Optional[str] = None
 100        self.comments: t.Optional[t.List[str]] = None
 101        self._type: t.Optional[DataType] = None
 102        self._meta: t.Optional[t.Dict[str, t.Any]] = None
 103        self._hash: t.Optional[int] = None
 104
 105        for arg_key, value in self.args.items():
 106            self._set_parent(arg_key, value)
 107
 108    def __eq__(self, other) -> bool:
 109        return type(self) is type(other) and hash(self) == hash(other)
 110
 111    @property
 112    def hashable_args(self) -> t.Any:
 113        return frozenset(
 114            (k, tuple(_norm_arg(a) for a in v) if type(v) is list else _norm_arg(v))
 115            for k, v in self.args.items()
 116            if not (v is None or v is False or (type(v) is list and not v))
 117        )
 118
 119    def __hash__(self) -> int:
 120        if self._hash is not None:
 121            return self._hash
 122
 123        return hash((self.__class__, self.hashable_args))
 124
 125    @property
 126    def this(self) -> t.Any:
 127        """
 128        Retrieves the argument with key "this".
 129        """
 130        return self.args.get("this")
 131
 132    @property
 133    def expression(self) -> t.Any:
 134        """
 135        Retrieves the argument with key "expression".
 136        """
 137        return self.args.get("expression")
 138
 139    @property
 140    def expressions(self) -> t.List[t.Any]:
 141        """
 142        Retrieves the argument with key "expressions".
 143        """
 144        return self.args.get("expressions") or []
 145
 146    def text(self, key) -> str:
 147        """
 148        Returns a textual representation of the argument corresponding to "key". This can only be used
 149        for args that are strings or leaf Expression instances, such as identifiers and literals.
 150        """
 151        field = self.args.get(key)
 152        if isinstance(field, str):
 153            return field
 154        if isinstance(field, (Identifier, Literal, Var)):
 155            return field.this
 156        if isinstance(field, (Star, Null)):
 157            return field.name
 158        return ""
 159
 160    @property
 161    def is_string(self) -> bool:
 162        """
 163        Checks whether a Literal expression is a string.
 164        """
 165        return isinstance(self, Literal) and self.args["is_string"]
 166
 167    @property
 168    def is_number(self) -> bool:
 169        """
 170        Checks whether a Literal expression is a number.
 171        """
 172        return isinstance(self, Literal) and not self.args["is_string"]
 173
 174    @property
 175    def is_int(self) -> bool:
 176        """
 177        Checks whether a Literal expression is an integer.
 178        """
 179        return self.is_number and is_int(self.name)
 180
 181    @property
 182    def is_star(self) -> bool:
 183        """Checks whether an expression is a star."""
 184        return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star))
 185
 186    @property
 187    def alias(self) -> str:
 188        """
 189        Returns the alias of the expression, or an empty string if it's not aliased.
 190        """
 191        if isinstance(self.args.get("alias"), TableAlias):
 192            return self.args["alias"].name
 193        return self.text("alias")
 194
 195    @property
 196    def alias_column_names(self) -> t.List[str]:
 197        table_alias = self.args.get("alias")
 198        if not table_alias:
 199            return []
 200        return [c.name for c in table_alias.args.get("columns") or []]
 201
 202    @property
 203    def name(self) -> str:
 204        return self.text("this")
 205
 206    @property
 207    def alias_or_name(self) -> str:
 208        return self.alias or self.name
 209
 210    @property
 211    def output_name(self) -> str:
 212        """
 213        Name of the output column if this expression is a selection.
 214
 215        If the Expression has no output name, an empty string is returned.
 216
 217        Example:
 218            >>> from sqlglot import parse_one
 219            >>> parse_one("SELECT a").expressions[0].output_name
 220            'a'
 221            >>> parse_one("SELECT b AS c").expressions[0].output_name
 222            'c'
 223            >>> parse_one("SELECT 1 + 2").expressions[0].output_name
 224            ''
 225        """
 226        return ""
 227
 228    @property
 229    def type(self) -> t.Optional[DataType]:
 230        return self._type
 231
 232    @type.setter
 233    def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None:
 234        if dtype and not isinstance(dtype, DataType):
 235            dtype = DataType.build(dtype)
 236        self._type = dtype  # type: ignore
 237
 238    def is_type(self, *dtypes) -> bool:
 239        return self.type is not None and self.type.is_type(*dtypes)
 240
 241    def is_leaf(self) -> bool:
 242        return not any(isinstance(v, (Expression, list)) for v in self.args.values())
 243
 244    @property
 245    def meta(self) -> t.Dict[str, t.Any]:
 246        if self._meta is None:
 247            self._meta = {}
 248        return self._meta
 249
 250    def __deepcopy__(self, memo):
 251        copy = self.__class__(**deepcopy(self.args))
 252        if self.comments is not None:
 253            copy.comments = deepcopy(self.comments)
 254
 255        if self._type is not None:
 256            copy._type = self._type.copy()
 257
 258        if self._meta is not None:
 259            copy._meta = deepcopy(self._meta)
 260
 261        return copy
 262
 263    def copy(self):
 264        """
 265        Returns a deep copy of the expression.
 266        """
 267        new = deepcopy(self)
 268        new.parent = self.parent
 269        return new
 270
 271    def add_comments(self, comments: t.Optional[t.List[str]]) -> None:
 272        if self.comments is None:
 273            self.comments = []
 274        if comments:
 275            for comment in comments:
 276                _, *meta = comment.split(SQLGLOT_META)
 277                if meta:
 278                    for kv in "".join(meta).split(","):
 279                        k, *v = kv.split("=")
 280                        value = v[0].strip() if v else True
 281                        self.meta[k.strip()] = value
 282                self.comments.append(comment)
 283
 284    def append(self, arg_key: str, value: t.Any) -> None:
 285        """
 286        Appends value to arg_key if it's a list or sets it as a new list.
 287
 288        Args:
 289            arg_key (str): name of the list expression arg
 290            value (Any): value to append to the list
 291        """
 292        if not isinstance(self.args.get(arg_key), list):
 293            self.args[arg_key] = []
 294        self.args[arg_key].append(value)
 295        self._set_parent(arg_key, value)
 296
 297    def set(self, arg_key: str, value: t.Any) -> None:
 298        """
 299        Sets arg_key to value.
 300
 301        Args:
 302            arg_key: name of the expression arg.
 303            value: value to set the arg to.
 304        """
 305        if value is None:
 306            self.args.pop(arg_key, None)
 307            return
 308
 309        self.args[arg_key] = value
 310        self._set_parent(arg_key, value)
 311
 312    def _set_parent(self, arg_key: str, value: t.Any) -> None:
 313        if hasattr(value, "parent"):
 314            value.parent = self
 315            value.arg_key = arg_key
 316        elif type(value) is list:
 317            for v in value:
 318                if hasattr(v, "parent"):
 319                    v.parent = self
 320                    v.arg_key = arg_key
 321
 322    @property
 323    def depth(self) -> int:
 324        """
 325        Returns the depth of this tree.
 326        """
 327        if self.parent:
 328            return self.parent.depth + 1
 329        return 0
 330
 331    def iter_expressions(self) -> t.Iterator[t.Tuple[str, Expression]]:
 332        """Yields the key and expression for all arguments, exploding list args."""
 333        for k, vs in self.args.items():
 334            if type(vs) is list:
 335                for v in vs:
 336                    if hasattr(v, "parent"):
 337                        yield k, v
 338            else:
 339                if hasattr(vs, "parent"):
 340                    yield k, vs
 341
 342    def find(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Optional[E]:
 343        """
 344        Returns the first node in this tree which matches at least one of
 345        the specified types.
 346
 347        Args:
 348            expression_types: the expression type(s) to match.
 349            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 350
 351        Returns:
 352            The node which matches the criteria or None if no such node was found.
 353        """
 354        return next(self.find_all(*expression_types, bfs=bfs), None)
 355
 356    def find_all(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Iterator[E]:
 357        """
 358        Returns a generator object which visits all nodes in this tree and only
 359        yields those that match at least one of the specified expression types.
 360
 361        Args:
 362            expression_types: the expression type(s) to match.
 363            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 364
 365        Returns:
 366            The generator object.
 367        """
 368        for expression, *_ in self.walk(bfs=bfs):
 369            if isinstance(expression, expression_types):
 370                yield expression
 371
 372    def find_ancestor(self, *expression_types: t.Type[E]) -> t.Optional[E]:
 373        """
 374        Returns a nearest parent matching expression_types.
 375
 376        Args:
 377            expression_types: the expression type(s) to match.
 378
 379        Returns:
 380            The parent node.
 381        """
 382        ancestor = self.parent
 383        while ancestor and not isinstance(ancestor, expression_types):
 384            ancestor = ancestor.parent
 385        return ancestor  # type: ignore
 386
 387    @property
 388    def parent_select(self) -> t.Optional[Select]:
 389        """
 390        Returns the parent select statement.
 391        """
 392        return self.find_ancestor(Select)
 393
 394    @property
 395    def same_parent(self) -> bool:
 396        """Returns if the parent is the same class as itself."""
 397        return type(self.parent) is self.__class__
 398
 399    def root(self) -> Expression:
 400        """
 401        Returns the root expression of this tree.
 402        """
 403        expression = self
 404        while expression.parent:
 405            expression = expression.parent
 406        return expression
 407
 408    def walk(self, bfs=True, prune=None):
 409        """
 410        Returns a generator object which visits all nodes in this tree.
 411
 412        Args:
 413            bfs (bool): if set to True the BFS traversal order will be applied,
 414                otherwise the DFS traversal will be used instead.
 415            prune ((node, parent, arg_key) -> bool): callable that returns True if
 416                the generator should stop traversing this branch of the tree.
 417
 418        Returns:
 419            the generator object.
 420        """
 421        if bfs:
 422            yield from self.bfs(prune=prune)
 423        else:
 424            yield from self.dfs(prune=prune)
 425
 426    def dfs(self, parent=None, key=None, prune=None):
 427        """
 428        Returns a generator object which visits all nodes in this tree in
 429        the DFS (Depth-first) order.
 430
 431        Returns:
 432            The generator object.
 433        """
 434        parent = parent or self.parent
 435        yield self, parent, key
 436        if prune and prune(self, parent, key):
 437            return
 438
 439        for k, v in self.iter_expressions():
 440            yield from v.dfs(self, k, prune)
 441
 442    def bfs(self, prune=None):
 443        """
 444        Returns a generator object which visits all nodes in this tree in
 445        the BFS (Breadth-first) order.
 446
 447        Returns:
 448            The generator object.
 449        """
 450        queue = deque([(self, self.parent, None)])
 451
 452        while queue:
 453            item, parent, key = queue.popleft()
 454
 455            yield item, parent, key
 456            if prune and prune(item, parent, key):
 457                continue
 458
 459            for k, v in item.iter_expressions():
 460                queue.append((v, item, k))
 461
 462    def unnest(self):
 463        """
 464        Returns the first non parenthesis child or self.
 465        """
 466        expression = self
 467        while type(expression) is Paren:
 468            expression = expression.this
 469        return expression
 470
 471    def unalias(self):
 472        """
 473        Returns the inner expression if this is an Alias.
 474        """
 475        if isinstance(self, Alias):
 476            return self.this
 477        return self
 478
 479    def unnest_operands(self):
 480        """
 481        Returns unnested operands as a tuple.
 482        """
 483        return tuple(arg.unnest() for _, arg in self.iter_expressions())
 484
 485    def flatten(self, unnest=True):
 486        """
 487        Returns a generator which yields child nodes whose parents are the same class.
 488
 489        A AND B AND C -> [A, B, C]
 490        """
 491        for node, _, _ in self.dfs(prune=lambda n, p, *_: p and type(n) is not self.__class__):
 492            if type(node) is not self.__class__:
 493                yield node.unnest() if unnest and not isinstance(node, Subquery) else node
 494
 495    def __str__(self) -> str:
 496        return self.sql()
 497
 498    def __repr__(self) -> str:
 499        return _to_s(self)
 500
 501    def to_s(self) -> str:
 502        """
 503        Same as __repr__, but includes additional information which can be useful
 504        for debugging, like empty or missing args and the AST nodes' object IDs.
 505        """
 506        return _to_s(self, verbose=True)
 507
 508    def sql(self, dialect: DialectType = None, **opts) -> str:
 509        """
 510        Returns SQL string representation of this tree.
 511
 512        Args:
 513            dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql").
 514            opts: other `sqlglot.generator.Generator` options.
 515
 516        Returns:
 517            The SQL string.
 518        """
 519        from sqlglot.dialects import Dialect
 520
 521        return Dialect.get_or_raise(dialect).generate(self, **opts)
 522
 523    def transform(self, fun, *args, copy=True, **kwargs):
 524        """
 525        Recursively visits all tree nodes (excluding already transformed ones)
 526        and applies the given transformation function to each node.
 527
 528        Args:
 529            fun (function): a function which takes a node as an argument and returns a
 530                new transformed node or the same node without modifications. If the function
 531                returns None, then the corresponding node will be removed from the syntax tree.
 532            copy (bool): if set to True a new tree instance is constructed, otherwise the tree is
 533                modified in place.
 534
 535        Returns:
 536            The transformed tree.
 537        """
 538        node = self.copy() if copy else self
 539        new_node = fun(node, *args, **kwargs)
 540
 541        if new_node is None or not isinstance(new_node, Expression):
 542            return new_node
 543        if new_node is not node:
 544            new_node.parent = node.parent
 545            return new_node
 546
 547        replace_children(new_node, lambda child: child.transform(fun, *args, copy=False, **kwargs))
 548        return new_node
 549
 550    @t.overload
 551    def replace(self, expression: E) -> E:
 552        ...
 553
 554    @t.overload
 555    def replace(self, expression: None) -> None:
 556        ...
 557
 558    def replace(self, expression):
 559        """
 560        Swap out this expression with a new expression.
 561
 562        For example::
 563
 564            >>> tree = Select().select("x").from_("tbl")
 565            >>> tree.find(Column).replace(column("y"))
 566            Column(
 567              this=Identifier(this=y, quoted=False))
 568            >>> tree.sql()
 569            'SELECT y FROM tbl'
 570
 571        Args:
 572            expression: new node
 573
 574        Returns:
 575            The new expression or expressions.
 576        """
 577        if not self.parent:
 578            return expression
 579
 580        parent = self.parent
 581        self.parent = None
 582
 583        replace_children(parent, lambda child: expression if child is self else child)
 584        return expression
 585
 586    def pop(self: E) -> E:
 587        """
 588        Remove this expression from its AST.
 589
 590        Returns:
 591            The popped expression.
 592        """
 593        self.replace(None)
 594        return self
 595
 596    def assert_is(self, type_: t.Type[E]) -> E:
 597        """
 598        Assert that this `Expression` is an instance of `type_`.
 599
 600        If it is NOT an instance of `type_`, this raises an assertion error.
 601        Otherwise, this returns this expression.
 602
 603        Examples:
 604            This is useful for type security in chained expressions:
 605
 606            >>> import sqlglot
 607            >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql()
 608            'SELECT x, z FROM y'
 609        """
 610        if not isinstance(self, type_):
 611            raise AssertionError(f"{self} is not {type_}.")
 612        return self
 613
 614    def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]:
 615        """
 616        Checks if this expression is valid (e.g. all mandatory args are set).
 617
 618        Args:
 619            args: a sequence of values that were used to instantiate a Func expression. This is used
 620                to check that the provided arguments don't exceed the function argument limit.
 621
 622        Returns:
 623            A list of error messages for all possible errors that were found.
 624        """
 625        errors: t.List[str] = []
 626
 627        for k in self.args:
 628            if k not in self.arg_types:
 629                errors.append(f"Unexpected keyword: '{k}' for {self.__class__}")
 630        for k, mandatory in self.arg_types.items():
 631            v = self.args.get(k)
 632            if mandatory and (v is None or (isinstance(v, list) and not v)):
 633                errors.append(f"Required keyword: '{k}' missing for {self.__class__}")
 634
 635        if (
 636            args
 637            and isinstance(self, Func)
 638            and len(args) > len(self.arg_types)
 639            and not self.is_var_len_args
 640        ):
 641            errors.append(
 642                f"The number of provided arguments ({len(args)}) is greater than "
 643                f"the maximum number of supported arguments ({len(self.arg_types)})"
 644            )
 645
 646        return errors
 647
 648    def dump(self):
 649        """
 650        Dump this Expression to a JSON-serializable dict.
 651        """
 652        from sqlglot.serde import dump
 653
 654        return dump(self)
 655
 656    @classmethod
 657    def load(cls, obj):
 658        """
 659        Load a dict (as returned by `Expression.dump`) into an Expression instance.
 660        """
 661        from sqlglot.serde import load
 662
 663        return load(obj)
 664
 665    def and_(
 666        self,
 667        *expressions: t.Optional[ExpOrStr],
 668        dialect: DialectType = None,
 669        copy: bool = True,
 670        **opts,
 671    ) -> Condition:
 672        """
 673        AND this condition with one or multiple expressions.
 674
 675        Example:
 676            >>> condition("x=1").and_("y=1").sql()
 677            'x = 1 AND y = 1'
 678
 679        Args:
 680            *expressions: the SQL code strings to parse.
 681                If an `Expression` instance is passed, it will be used as-is.
 682            dialect: the dialect used to parse the input expression.
 683            copy: whether to copy the involved expressions (only applies to Expressions).
 684            opts: other options to use to parse the input expressions.
 685
 686        Returns:
 687            The new And condition.
 688        """
 689        return and_(self, *expressions, dialect=dialect, copy=copy, **opts)
 690
 691    def or_(
 692        self,
 693        *expressions: t.Optional[ExpOrStr],
 694        dialect: DialectType = None,
 695        copy: bool = True,
 696        **opts,
 697    ) -> Condition:
 698        """
 699        OR this condition with one or multiple expressions.
 700
 701        Example:
 702            >>> condition("x=1").or_("y=1").sql()
 703            'x = 1 OR y = 1'
 704
 705        Args:
 706            *expressions: the SQL code strings to parse.
 707                If an `Expression` instance is passed, it will be used as-is.
 708            dialect: the dialect used to parse the input expression.
 709            copy: whether to copy the involved expressions (only applies to Expressions).
 710            opts: other options to use to parse the input expressions.
 711
 712        Returns:
 713            The new Or condition.
 714        """
 715        return or_(self, *expressions, dialect=dialect, copy=copy, **opts)
 716
 717    def not_(self, copy: bool = True):
 718        """
 719        Wrap this condition with NOT.
 720
 721        Example:
 722            >>> condition("x=1").not_().sql()
 723            'NOT x = 1'
 724
 725        Args:
 726            copy: whether to copy this object.
 727
 728        Returns:
 729            The new Not instance.
 730        """
 731        return not_(self, copy=copy)
 732
 733    def as_(
 734        self,
 735        alias: str | Identifier,
 736        quoted: t.Optional[bool] = None,
 737        dialect: DialectType = None,
 738        copy: bool = True,
 739        **opts,
 740    ) -> Alias:
 741        return alias_(self, alias, quoted=quoted, dialect=dialect, copy=copy, **opts)
 742
 743    def _binop(self, klass: t.Type[E], other: t.Any, reverse: bool = False) -> E:
 744        this = self.copy()
 745        other = convert(other, copy=True)
 746        if not isinstance(this, klass) and not isinstance(other, klass):
 747            this = _wrap(this, Binary)
 748            other = _wrap(other, Binary)
 749        if reverse:
 750            return klass(this=other, expression=this)
 751        return klass(this=this, expression=other)
 752
 753    def __getitem__(self, other: ExpOrStr | t.Tuple[ExpOrStr]) -> Bracket:
 754        return Bracket(
 755            this=self.copy(), expressions=[convert(e, copy=True) for e in ensure_list(other)]
 756        )
 757
 758    def __iter__(self) -> t.Iterator:
 759        if "expressions" in self.arg_types:
 760            return iter(self.args.get("expressions") or [])
 761        # We define this because __getitem__ converts Expression into an iterable, which is
 762        # problematic because one can hit infinite loops if they do "for x in some_expr: ..."
 763        # See: https://peps.python.org/pep-0234/
 764        raise TypeError(f"'{self.__class__.__name__}' object is not iterable")
 765
 766    def isin(
 767        self,
 768        *expressions: t.Any,
 769        query: t.Optional[ExpOrStr] = None,
 770        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
 771        copy: bool = True,
 772        **opts,
 773    ) -> In:
 774        return In(
 775            this=maybe_copy(self, copy),
 776            expressions=[convert(e, copy=copy) for e in expressions],
 777            query=maybe_parse(query, copy=copy, **opts) if query else None,
 778            unnest=(
 779                Unnest(
 780                    expressions=[
 781                        maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts)
 782                        for e in ensure_list(unnest)
 783                    ]
 784                )
 785                if unnest
 786                else None
 787            ),
 788        )
 789
 790    def between(self, low: t.Any, high: t.Any, copy: bool = True, **opts) -> Between:
 791        return Between(
 792            this=maybe_copy(self, copy),
 793            low=convert(low, copy=copy, **opts),
 794            high=convert(high, copy=copy, **opts),
 795        )
 796
 797    def is_(self, other: ExpOrStr) -> Is:
 798        return self._binop(Is, other)
 799
 800    def like(self, other: ExpOrStr) -> Like:
 801        return self._binop(Like, other)
 802
 803    def ilike(self, other: ExpOrStr) -> ILike:
 804        return self._binop(ILike, other)
 805
 806    def eq(self, other: t.Any) -> EQ:
 807        return self._binop(EQ, other)
 808
 809    def neq(self, other: t.Any) -> NEQ:
 810        return self._binop(NEQ, other)
 811
 812    def rlike(self, other: ExpOrStr) -> RegexpLike:
 813        return self._binop(RegexpLike, other)
 814
 815    def div(self, other: ExpOrStr, typed: bool = False, safe: bool = False) -> Div:
 816        div = self._binop(Div, other)
 817        div.args["typed"] = typed
 818        div.args["safe"] = safe
 819        return div
 820
 821    def desc(self, nulls_first: bool = False) -> Ordered:
 822        return Ordered(this=self.copy(), desc=True, nulls_first=nulls_first)
 823
 824    def __lt__(self, other: t.Any) -> LT:
 825        return self._binop(LT, other)
 826
 827    def __le__(self, other: t.Any) -> LTE:
 828        return self._binop(LTE, other)
 829
 830    def __gt__(self, other: t.Any) -> GT:
 831        return self._binop(GT, other)
 832
 833    def __ge__(self, other: t.Any) -> GTE:
 834        return self._binop(GTE, other)
 835
 836    def __add__(self, other: t.Any) -> Add:
 837        return self._binop(Add, other)
 838
 839    def __radd__(self, other: t.Any) -> Add:
 840        return self._binop(Add, other, reverse=True)
 841
 842    def __sub__(self, other: t.Any) -> Sub:
 843        return self._binop(Sub, other)
 844
 845    def __rsub__(self, other: t.Any) -> Sub:
 846        return self._binop(Sub, other, reverse=True)
 847
 848    def __mul__(self, other: t.Any) -> Mul:
 849        return self._binop(Mul, other)
 850
 851    def __rmul__(self, other: t.Any) -> Mul:
 852        return self._binop(Mul, other, reverse=True)
 853
 854    def __truediv__(self, other: t.Any) -> Div:
 855        return self._binop(Div, other)
 856
 857    def __rtruediv__(self, other: t.Any) -> Div:
 858        return self._binop(Div, other, reverse=True)
 859
 860    def __floordiv__(self, other: t.Any) -> IntDiv:
 861        return self._binop(IntDiv, other)
 862
 863    def __rfloordiv__(self, other: t.Any) -> IntDiv:
 864        return self._binop(IntDiv, other, reverse=True)
 865
 866    def __mod__(self, other: t.Any) -> Mod:
 867        return self._binop(Mod, other)
 868
 869    def __rmod__(self, other: t.Any) -> Mod:
 870        return self._binop(Mod, other, reverse=True)
 871
 872    def __pow__(self, other: t.Any) -> Pow:
 873        return self._binop(Pow, other)
 874
 875    def __rpow__(self, other: t.Any) -> Pow:
 876        return self._binop(Pow, other, reverse=True)
 877
 878    def __and__(self, other: t.Any) -> And:
 879        return self._binop(And, other)
 880
 881    def __rand__(self, other: t.Any) -> And:
 882        return self._binop(And, other, reverse=True)
 883
 884    def __or__(self, other: t.Any) -> Or:
 885        return self._binop(Or, other)
 886
 887    def __ror__(self, other: t.Any) -> Or:
 888        return self._binop(Or, other, reverse=True)
 889
 890    def __neg__(self) -> Neg:
 891        return Neg(this=_wrap(self.copy(), Binary))
 892
 893    def __invert__(self) -> Not:
 894        return not_(self.copy())
 895
 896
 897IntoType = t.Union[
 898    str,
 899    t.Type[Expression],
 900    t.Collection[t.Union[str, t.Type[Expression]]],
 901]
 902ExpOrStr = t.Union[str, Expression]
 903
 904
 905class Condition(Expression):
 906    """Logical conditions like x AND y, or simply x"""
 907
 908
 909class Predicate(Condition):
 910    """Relationships like x = y, x > 1, x >= y."""
 911
 912
 913class DerivedTable(Expression):
 914    @property
 915    def selects(self) -> t.List[Expression]:
 916        return self.this.selects if isinstance(self.this, Query) else []
 917
 918    @property
 919    def named_selects(self) -> t.List[str]:
 920        return [select.output_name for select in self.selects]
 921
 922
 923class Query(Expression):
 924    def subquery(self, alias: t.Optional[ExpOrStr] = None, copy: bool = True) -> Subquery:
 925        """
 926        Returns a `Subquery` that wraps around this query.
 927
 928        Example:
 929            >>> subquery = Select().select("x").from_("tbl").subquery()
 930            >>> Select().select("x").from_(subquery).sql()
 931            'SELECT x FROM (SELECT x FROM tbl)'
 932
 933        Args:
 934            alias: an optional alias for the subquery.
 935            copy: if `False`, modify this expression instance in-place.
 936        """
 937        instance = maybe_copy(self, copy)
 938        if not isinstance(alias, Expression):
 939            alias = TableAlias(this=to_identifier(alias)) if alias else None
 940
 941        return Subquery(this=instance, alias=alias)
 942
 943    def limit(
 944        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
 945    ) -> Select:
 946        """
 947        Adds a LIMIT clause to this query.
 948
 949        Example:
 950            >>> select("1").union(select("1")).limit(1).sql()
 951            'SELECT * FROM (SELECT 1 UNION SELECT 1) AS _l_0 LIMIT 1'
 952
 953        Args:
 954            expression: the SQL code string to parse.
 955                This can also be an integer.
 956                If a `Limit` instance is passed, it will be used as-is.
 957                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
 958            dialect: the dialect used to parse the input expression.
 959            copy: if `False`, modify this expression instance in-place.
 960            opts: other options to use to parse the input expressions.
 961
 962        Returns:
 963            A limited Select expression.
 964        """
 965        return (
 966            select("*")
 967            .from_(self.subquery(alias="_l_0", copy=copy))
 968            .limit(expression, dialect=dialect, copy=False, **opts)
 969        )
 970
 971    @property
 972    def ctes(self) -> t.List[CTE]:
 973        """Returns a list of all the CTEs attached to this query."""
 974        with_ = self.args.get("with")
 975        return with_.expressions if with_ else []
 976
 977    @property
 978    def selects(self) -> t.List[Expression]:
 979        """Returns the query's projections."""
 980        raise NotImplementedError("Query objects must implement `selects`")
 981
 982    @property
 983    def named_selects(self) -> t.List[str]:
 984        """Returns the output names of the query's projections."""
 985        raise NotImplementedError("Query objects must implement `named_selects`")
 986
 987    def select(
 988        self,
 989        *expressions: t.Optional[ExpOrStr],
 990        append: bool = True,
 991        dialect: DialectType = None,
 992        copy: bool = True,
 993        **opts,
 994    ) -> Query:
 995        """
 996        Append to or set the SELECT expressions.
 997
 998        Example:
 999            >>> Select().select("x", "y").sql()
1000            'SELECT x, y'
1001
1002        Args:
1003            *expressions: the SQL code strings to parse.
1004                If an `Expression` instance is passed, it will be used as-is.
1005            append: if `True`, add to any existing expressions.
1006                Otherwise, this resets the expressions.
1007            dialect: the dialect used to parse the input expressions.
1008            copy: if `False`, modify this expression instance in-place.
1009            opts: other options to use to parse the input expressions.
1010
1011        Returns:
1012            The modified Query expression.
1013        """
1014        raise NotImplementedError("Query objects must implement `select`")
1015
1016    def with_(
1017        self,
1018        alias: ExpOrStr,
1019        as_: ExpOrStr,
1020        recursive: t.Optional[bool] = None,
1021        append: bool = True,
1022        dialect: DialectType = None,
1023        copy: bool = True,
1024        **opts,
1025    ) -> Query:
1026        """
1027        Append to or set the common table expressions.
1028
1029        Example:
1030            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
1031            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
1032
1033        Args:
1034            alias: the SQL code string to parse as the table name.
1035                If an `Expression` instance is passed, this is used as-is.
1036            as_: the SQL code string to parse as the table expression.
1037                If an `Expression` instance is passed, it will be used as-is.
1038            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
1039            append: if `True`, add to any existing expressions.
1040                Otherwise, this resets the expressions.
1041            dialect: the dialect used to parse the input expression.
1042            copy: if `False`, modify this expression instance in-place.
1043            opts: other options to use to parse the input expressions.
1044
1045        Returns:
1046            The modified expression.
1047        """
1048        return _apply_cte_builder(
1049            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
1050        )
1051
1052    def union(
1053        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
1054    ) -> Union:
1055        """
1056        Builds a UNION expression.
1057
1058        Example:
1059            >>> import sqlglot
1060            >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql()
1061            'SELECT * FROM foo UNION SELECT * FROM bla'
1062
1063        Args:
1064            expression: the SQL code string.
1065                If an `Expression` instance is passed, it will be used as-is.
1066            distinct: set the DISTINCT flag if and only if this is true.
1067            dialect: the dialect used to parse the input expression.
1068            opts: other options to use to parse the input expressions.
1069
1070        Returns:
1071            The new Union expression.
1072        """
1073        return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1074
1075    def intersect(
1076        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
1077    ) -> Intersect:
1078        """
1079        Builds an INTERSECT expression.
1080
1081        Example:
1082            >>> import sqlglot
1083            >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql()
1084            'SELECT * FROM foo INTERSECT SELECT * FROM bla'
1085
1086        Args:
1087            expression: the SQL code string.
1088                If an `Expression` instance is passed, it will be used as-is.
1089            distinct: set the DISTINCT flag if and only if this is true.
1090            dialect: the dialect used to parse the input expression.
1091            opts: other options to use to parse the input expressions.
1092
1093        Returns:
1094            The new Intersect expression.
1095        """
1096        return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1097
1098    def except_(
1099        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
1100    ) -> Except:
1101        """
1102        Builds an EXCEPT expression.
1103
1104        Example:
1105            >>> import sqlglot
1106            >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql()
1107            'SELECT * FROM foo EXCEPT SELECT * FROM bla'
1108
1109        Args:
1110            expression: the SQL code string.
1111                If an `Expression` instance is passed, it will be used as-is.
1112            distinct: set the DISTINCT flag if and only if this is true.
1113            dialect: the dialect used to parse the input expression.
1114            opts: other options to use to parse the input expressions.
1115
1116        Returns:
1117            The new Except expression.
1118        """
1119        return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1120
1121
1122class UDTF(DerivedTable):
1123    @property
1124    def selects(self) -> t.List[Expression]:
1125        alias = self.args.get("alias")
1126        return alias.columns if alias else []
1127
1128
1129class Cache(Expression):
1130    arg_types = {
1131        "this": True,
1132        "lazy": False,
1133        "options": False,
1134        "expression": False,
1135    }
1136
1137
1138class Uncache(Expression):
1139    arg_types = {"this": True, "exists": False}
1140
1141
1142class Refresh(Expression):
1143    pass
1144
1145
1146class DDL(Expression):
1147    @property
1148    def ctes(self) -> t.List[CTE]:
1149        """Returns a list of all the CTEs attached to this statement."""
1150        with_ = self.args.get("with")
1151        return with_.expressions if with_ else []
1152
1153    @property
1154    def selects(self) -> t.List[Expression]:
1155        """If this statement contains a query (e.g. a CTAS), this returns the query's projections."""
1156        return self.expression.selects if isinstance(self.expression, Query) else []
1157
1158    @property
1159    def named_selects(self) -> t.List[str]:
1160        """
1161        If this statement contains a query (e.g. a CTAS), this returns the output
1162        names of the query's projections.
1163        """
1164        return self.expression.named_selects if isinstance(self.expression, Query) else []
1165
1166
1167class DML(Expression):
1168    def returning(
1169        self,
1170        expression: ExpOrStr,
1171        dialect: DialectType = None,
1172        copy: bool = True,
1173        **opts,
1174    ) -> DML:
1175        """
1176        Set the RETURNING expression. Not supported by all dialects.
1177
1178        Example:
1179            >>> delete("tbl").returning("*", dialect="postgres").sql()
1180            'DELETE FROM tbl RETURNING *'
1181
1182        Args:
1183            expression: the SQL code strings to parse.
1184                If an `Expression` instance is passed, it will be used as-is.
1185            dialect: the dialect used to parse the input expressions.
1186            copy: if `False`, modify this expression instance in-place.
1187            opts: other options to use to parse the input expressions.
1188
1189        Returns:
1190            Delete: the modified expression.
1191        """
1192        return _apply_builder(
1193            expression=expression,
1194            instance=self,
1195            arg="returning",
1196            prefix="RETURNING",
1197            dialect=dialect,
1198            copy=copy,
1199            into=Returning,
1200            **opts,
1201        )
1202
1203
1204class Create(DDL):
1205    arg_types = {
1206        "with": False,
1207        "this": True,
1208        "kind": True,
1209        "expression": False,
1210        "exists": False,
1211        "properties": False,
1212        "replace": False,
1213        "unique": False,
1214        "indexes": False,
1215        "no_schema_binding": False,
1216        "begin": False,
1217        "end": False,
1218        "clone": False,
1219    }
1220
1221    @property
1222    def kind(self) -> t.Optional[str]:
1223        kind = self.args.get("kind")
1224        return kind and kind.upper()
1225
1226
1227class TruncateTable(Expression):
1228    arg_types = {
1229        "expressions": True,
1230        "is_database": False,
1231        "exists": False,
1232        "only": False,
1233        "cluster": False,
1234        "identity": False,
1235        "option": False,
1236        "partition": False,
1237    }
1238
1239
1240# https://docs.snowflake.com/en/sql-reference/sql/create-clone
1241# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_clone_statement
1242# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_copy
1243class Clone(Expression):
1244    arg_types = {"this": True, "shallow": False, "copy": False}
1245
1246
1247class Describe(Expression):
1248    arg_types = {"this": True, "extended": False, "kind": False, "expressions": False}
1249
1250
1251class Kill(Expression):
1252    arg_types = {"this": True, "kind": False}
1253
1254
1255class Pragma(Expression):
1256    pass
1257
1258
1259class Set(Expression):
1260    arg_types = {"expressions": False, "unset": False, "tag": False}
1261
1262
1263class Heredoc(Expression):
1264    arg_types = {"this": True, "tag": False}
1265
1266
1267class SetItem(Expression):
1268    arg_types = {
1269        "this": False,
1270        "expressions": False,
1271        "kind": False,
1272        "collate": False,  # MySQL SET NAMES statement
1273        "global": False,
1274    }
1275
1276
1277class Show(Expression):
1278    arg_types = {
1279        "this": True,
1280        "history": False,
1281        "terse": False,
1282        "target": False,
1283        "offset": False,
1284        "starts_with": False,
1285        "limit": False,
1286        "from": False,
1287        "like": False,
1288        "where": False,
1289        "db": False,
1290        "scope": False,
1291        "scope_kind": False,
1292        "full": False,
1293        "mutex": False,
1294        "query": False,
1295        "channel": False,
1296        "global": False,
1297        "log": False,
1298        "position": False,
1299        "types": False,
1300    }
1301
1302
1303class UserDefinedFunction(Expression):
1304    arg_types = {"this": True, "expressions": False, "wrapped": False}
1305
1306
1307class CharacterSet(Expression):
1308    arg_types = {"this": True, "default": False}
1309
1310
1311class With(Expression):
1312    arg_types = {"expressions": True, "recursive": False}
1313
1314    @property
1315    def recursive(self) -> bool:
1316        return bool(self.args.get("recursive"))
1317
1318
1319class WithinGroup(Expression):
1320    arg_types = {"this": True, "expression": False}
1321
1322
1323# clickhouse supports scalar ctes
1324# https://clickhouse.com/docs/en/sql-reference/statements/select/with
1325class CTE(DerivedTable):
1326    arg_types = {"this": True, "alias": True, "scalar": False}
1327
1328
1329class TableAlias(Expression):
1330    arg_types = {"this": False, "columns": False}
1331
1332    @property
1333    def columns(self):
1334        return self.args.get("columns") or []
1335
1336
1337class BitString(Condition):
1338    pass
1339
1340
1341class HexString(Condition):
1342    pass
1343
1344
1345class ByteString(Condition):
1346    pass
1347
1348
1349class RawString(Condition):
1350    pass
1351
1352
1353class UnicodeString(Condition):
1354    arg_types = {"this": True, "escape": False}
1355
1356
1357class Column(Condition):
1358    arg_types = {"this": True, "table": False, "db": False, "catalog": False, "join_mark": False}
1359
1360    @property
1361    def table(self) -> str:
1362        return self.text("table")
1363
1364    @property
1365    def db(self) -> str:
1366        return self.text("db")
1367
1368    @property
1369    def catalog(self) -> str:
1370        return self.text("catalog")
1371
1372    @property
1373    def output_name(self) -> str:
1374        return self.name
1375
1376    @property
1377    def parts(self) -> t.List[Identifier]:
1378        """Return the parts of a column in order catalog, db, table, name."""
1379        return [
1380            t.cast(Identifier, self.args[part])
1381            for part in ("catalog", "db", "table", "this")
1382            if self.args.get(part)
1383        ]
1384
1385    def to_dot(self) -> Dot | Identifier:
1386        """Converts the column into a dot expression."""
1387        parts = self.parts
1388        parent = self.parent
1389
1390        while parent:
1391            if isinstance(parent, Dot):
1392                parts.append(parent.expression)
1393            parent = parent.parent
1394
1395        return Dot.build(deepcopy(parts)) if len(parts) > 1 else parts[0]
1396
1397
1398class ColumnPosition(Expression):
1399    arg_types = {"this": False, "position": True}
1400
1401
1402class ColumnDef(Expression):
1403    arg_types = {
1404        "this": True,
1405        "kind": False,
1406        "constraints": False,
1407        "exists": False,
1408        "position": False,
1409    }
1410
1411    @property
1412    def constraints(self) -> t.List[ColumnConstraint]:
1413        return self.args.get("constraints") or []
1414
1415    @property
1416    def kind(self) -> t.Optional[DataType]:
1417        return self.args.get("kind")
1418
1419
1420class AlterColumn(Expression):
1421    arg_types = {
1422        "this": True,
1423        "dtype": False,
1424        "collate": False,
1425        "using": False,
1426        "default": False,
1427        "drop": False,
1428        "comment": False,
1429    }
1430
1431
1432class RenameColumn(Expression):
1433    arg_types = {"this": True, "to": True, "exists": False}
1434
1435
1436class RenameTable(Expression):
1437    pass
1438
1439
1440class SwapTable(Expression):
1441    pass
1442
1443
1444class Comment(Expression):
1445    arg_types = {"this": True, "kind": True, "expression": True, "exists": False}
1446
1447
1448class Comprehension(Expression):
1449    arg_types = {"this": True, "expression": True, "iterator": True, "condition": False}
1450
1451
1452# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1453class MergeTreeTTLAction(Expression):
1454    arg_types = {
1455        "this": True,
1456        "delete": False,
1457        "recompress": False,
1458        "to_disk": False,
1459        "to_volume": False,
1460    }
1461
1462
1463# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1464class MergeTreeTTL(Expression):
1465    arg_types = {
1466        "expressions": True,
1467        "where": False,
1468        "group": False,
1469        "aggregates": False,
1470    }
1471
1472
1473# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1474class IndexConstraintOption(Expression):
1475    arg_types = {
1476        "key_block_size": False,
1477        "using": False,
1478        "parser": False,
1479        "comment": False,
1480        "visible": False,
1481        "engine_attr": False,
1482        "secondary_engine_attr": False,
1483    }
1484
1485
1486class ColumnConstraint(Expression):
1487    arg_types = {"this": False, "kind": True}
1488
1489    @property
1490    def kind(self) -> ColumnConstraintKind:
1491        return self.args["kind"]
1492
1493
1494class ColumnConstraintKind(Expression):
1495    pass
1496
1497
1498class AutoIncrementColumnConstraint(ColumnConstraintKind):
1499    pass
1500
1501
1502class PeriodForSystemTimeConstraint(ColumnConstraintKind):
1503    arg_types = {"this": True, "expression": True}
1504
1505
1506class CaseSpecificColumnConstraint(ColumnConstraintKind):
1507    arg_types = {"not_": True}
1508
1509
1510class CharacterSetColumnConstraint(ColumnConstraintKind):
1511    arg_types = {"this": True}
1512
1513
1514class CheckColumnConstraint(ColumnConstraintKind):
1515    arg_types = {"this": True, "enforced": False}
1516
1517
1518class ClusteredColumnConstraint(ColumnConstraintKind):
1519    pass
1520
1521
1522class CollateColumnConstraint(ColumnConstraintKind):
1523    pass
1524
1525
1526class CommentColumnConstraint(ColumnConstraintKind):
1527    pass
1528
1529
1530class CompressColumnConstraint(ColumnConstraintKind):
1531    pass
1532
1533
1534class DateFormatColumnConstraint(ColumnConstraintKind):
1535    arg_types = {"this": True}
1536
1537
1538class DefaultColumnConstraint(ColumnConstraintKind):
1539    pass
1540
1541
1542class EncodeColumnConstraint(ColumnConstraintKind):
1543    pass
1544
1545
1546class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
1547    # this: True -> ALWAYS, this: False -> BY DEFAULT
1548    arg_types = {
1549        "this": False,
1550        "expression": False,
1551        "on_null": False,
1552        "start": False,
1553        "increment": False,
1554        "minvalue": False,
1555        "maxvalue": False,
1556        "cycle": False,
1557    }
1558
1559
1560class GeneratedAsRowColumnConstraint(ColumnConstraintKind):
1561    arg_types = {"start": False, "hidden": False}
1562
1563
1564# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1565class IndexColumnConstraint(ColumnConstraintKind):
1566    arg_types = {
1567        "this": False,
1568        "schema": True,
1569        "kind": False,
1570        "index_type": False,
1571        "options": False,
1572    }
1573
1574
1575class InlineLengthColumnConstraint(ColumnConstraintKind):
1576    pass
1577
1578
1579class NonClusteredColumnConstraint(ColumnConstraintKind):
1580    pass
1581
1582
1583class NotForReplicationColumnConstraint(ColumnConstraintKind):
1584    arg_types = {}
1585
1586
1587class NotNullColumnConstraint(ColumnConstraintKind):
1588    arg_types = {"allow_null": False}
1589
1590
1591# https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
1592class OnUpdateColumnConstraint(ColumnConstraintKind):
1593    pass
1594
1595
1596# https://docs.snowflake.com/en/sql-reference/sql/create-external-table#optional-parameters
1597class TransformColumnConstraint(ColumnConstraintKind):
1598    pass
1599
1600
1601class PrimaryKeyColumnConstraint(ColumnConstraintKind):
1602    arg_types = {"desc": False}
1603
1604
1605class TitleColumnConstraint(ColumnConstraintKind):
1606    pass
1607
1608
1609class UniqueColumnConstraint(ColumnConstraintKind):
1610    arg_types = {"this": False, "index_type": False}
1611
1612
1613class UppercaseColumnConstraint(ColumnConstraintKind):
1614    arg_types: t.Dict[str, t.Any] = {}
1615
1616
1617class PathColumnConstraint(ColumnConstraintKind):
1618    pass
1619
1620
1621# computed column expression
1622# https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16
1623class ComputedColumnConstraint(ColumnConstraintKind):
1624    arg_types = {"this": True, "persisted": False, "not_null": False}
1625
1626
1627class Constraint(Expression):
1628    arg_types = {"this": True, "expressions": True}
1629
1630
1631class Delete(DML):
1632    arg_types = {
1633        "with": False,
1634        "this": False,
1635        "using": False,
1636        "where": False,
1637        "returning": False,
1638        "limit": False,
1639        "tables": False,  # Multiple-Table Syntax (MySQL)
1640    }
1641
1642    def delete(
1643        self,
1644        table: ExpOrStr,
1645        dialect: DialectType = None,
1646        copy: bool = True,
1647        **opts,
1648    ) -> Delete:
1649        """
1650        Create a DELETE expression or replace the table on an existing DELETE expression.
1651
1652        Example:
1653            >>> delete("tbl").sql()
1654            'DELETE FROM tbl'
1655
1656        Args:
1657            table: the table from which to delete.
1658            dialect: the dialect used to parse the input expression.
1659            copy: if `False`, modify this expression instance in-place.
1660            opts: other options to use to parse the input expressions.
1661
1662        Returns:
1663            Delete: the modified expression.
1664        """
1665        return _apply_builder(
1666            expression=table,
1667            instance=self,
1668            arg="this",
1669            dialect=dialect,
1670            into=Table,
1671            copy=copy,
1672            **opts,
1673        )
1674
1675    def where(
1676        self,
1677        *expressions: t.Optional[ExpOrStr],
1678        append: bool = True,
1679        dialect: DialectType = None,
1680        copy: bool = True,
1681        **opts,
1682    ) -> Delete:
1683        """
1684        Append to or set the WHERE expressions.
1685
1686        Example:
1687            >>> delete("tbl").where("x = 'a' OR x < 'b'").sql()
1688            "DELETE FROM tbl WHERE x = 'a' OR x < 'b'"
1689
1690        Args:
1691            *expressions: the SQL code strings to parse.
1692                If an `Expression` instance is passed, it will be used as-is.
1693                Multiple expressions are combined with an AND operator.
1694            append: if `True`, AND the new expressions to any existing expression.
1695                Otherwise, this resets the expression.
1696            dialect: the dialect used to parse the input expressions.
1697            copy: if `False`, modify this expression instance in-place.
1698            opts: other options to use to parse the input expressions.
1699
1700        Returns:
1701            Delete: the modified expression.
1702        """
1703        return _apply_conjunction_builder(
1704            *expressions,
1705            instance=self,
1706            arg="where",
1707            append=append,
1708            into=Where,
1709            dialect=dialect,
1710            copy=copy,
1711            **opts,
1712        )
1713
1714
1715class Drop(Expression):
1716    arg_types = {
1717        "this": False,
1718        "kind": False,
1719        "exists": False,
1720        "temporary": False,
1721        "materialized": False,
1722        "cascade": False,
1723        "constraints": False,
1724        "purge": False,
1725    }
1726
1727
1728class Filter(Expression):
1729    arg_types = {"this": True, "expression": True}
1730
1731
1732class Check(Expression):
1733    pass
1734
1735
1736# https://docs.snowflake.com/en/sql-reference/constructs/connect-by
1737class Connect(Expression):
1738    arg_types = {"start": False, "connect": True}
1739
1740
1741class Prior(Expression):
1742    pass
1743
1744
1745class Directory(Expression):
1746    # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html
1747    arg_types = {"this": True, "local": False, "row_format": False}
1748
1749
1750class ForeignKey(Expression):
1751    arg_types = {
1752        "expressions": True,
1753        "reference": False,
1754        "delete": False,
1755        "update": False,
1756    }
1757
1758
1759class ColumnPrefix(Expression):
1760    arg_types = {"this": True, "expression": True}
1761
1762
1763class PrimaryKey(Expression):
1764    arg_types = {"expressions": True, "options": False}
1765
1766
1767# https://www.postgresql.org/docs/9.1/sql-selectinto.html
1768# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples
1769class Into(Expression):
1770    arg_types = {"this": True, "temporary": False, "unlogged": False}
1771
1772
1773class From(Expression):
1774    @property
1775    def name(self) -> str:
1776        return self.this.name
1777
1778    @property
1779    def alias_or_name(self) -> str:
1780        return self.this.alias_or_name
1781
1782
1783class Having(Expression):
1784    pass
1785
1786
1787class Hint(Expression):
1788    arg_types = {"expressions": True}
1789
1790
1791class JoinHint(Expression):
1792    arg_types = {"this": True, "expressions": True}
1793
1794
1795class Identifier(Expression):
1796    arg_types = {"this": True, "quoted": False, "global": False, "temporary": False}
1797
1798    @property
1799    def quoted(self) -> bool:
1800        return bool(self.args.get("quoted"))
1801
1802    @property
1803    def hashable_args(self) -> t.Any:
1804        return (self.this, self.quoted)
1805
1806    @property
1807    def output_name(self) -> str:
1808        return self.name
1809
1810
1811# https://www.postgresql.org/docs/current/indexes-opclass.html
1812class Opclass(Expression):
1813    arg_types = {"this": True, "expression": True}
1814
1815
1816class Index(Expression):
1817    arg_types = {
1818        "this": False,
1819        "table": False,
1820        "using": False,
1821        "where": False,
1822        "columns": False,
1823        "unique": False,
1824        "primary": False,
1825        "amp": False,  # teradata
1826        "include": False,
1827        "partition_by": False,  # teradata
1828    }
1829
1830
1831class Insert(DDL, DML):
1832    arg_types = {
1833        "with": False,
1834        "this": True,
1835        "expression": False,
1836        "conflict": False,
1837        "returning": False,
1838        "overwrite": False,
1839        "exists": False,
1840        "partition": False,
1841        "alternative": False,
1842        "where": False,
1843        "ignore": False,
1844        "by_name": False,
1845    }
1846
1847    def with_(
1848        self,
1849        alias: ExpOrStr,
1850        as_: ExpOrStr,
1851        recursive: t.Optional[bool] = None,
1852        append: bool = True,
1853        dialect: DialectType = None,
1854        copy: bool = True,
1855        **opts,
1856    ) -> Insert:
1857        """
1858        Append to or set the common table expressions.
1859
1860        Example:
1861            >>> insert("SELECT x FROM cte", "t").with_("cte", as_="SELECT * FROM tbl").sql()
1862            'WITH cte AS (SELECT * FROM tbl) INSERT INTO t SELECT x FROM cte'
1863
1864        Args:
1865            alias: the SQL code string to parse as the table name.
1866                If an `Expression` instance is passed, this is used as-is.
1867            as_: the SQL code string to parse as the table expression.
1868                If an `Expression` instance is passed, it will be used as-is.
1869            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
1870            append: if `True`, add to any existing expressions.
1871                Otherwise, this resets the expressions.
1872            dialect: the dialect used to parse the input expression.
1873            copy: if `False`, modify this expression instance in-place.
1874            opts: other options to use to parse the input expressions.
1875
1876        Returns:
1877            The modified expression.
1878        """
1879        return _apply_cte_builder(
1880            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
1881        )
1882
1883
1884class OnConflict(Expression):
1885    arg_types = {
1886        "duplicate": False,
1887        "expressions": False,
1888        "nothing": False,
1889        "key": False,
1890        "constraint": False,
1891    }
1892
1893
1894class Returning(Expression):
1895    arg_types = {"expressions": True, "into": False}
1896
1897
1898# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
1899class Introducer(Expression):
1900    arg_types = {"this": True, "expression": True}
1901
1902
1903# national char, like n'utf8'
1904class National(Expression):
1905    pass
1906
1907
1908class LoadData(Expression):
1909    arg_types = {
1910        "this": True,
1911        "local": False,
1912        "overwrite": False,
1913        "inpath": True,
1914        "partition": False,
1915        "input_format": False,
1916        "serde": False,
1917    }
1918
1919
1920class Partition(Expression):
1921    arg_types = {"expressions": True}
1922
1923
1924class PartitionRange(Expression):
1925    arg_types = {"this": True, "expression": True}
1926
1927
1928class Fetch(Expression):
1929    arg_types = {
1930        "direction": False,
1931        "count": False,
1932        "percent": False,
1933        "with_ties": False,
1934    }
1935
1936
1937class Group(Expression):
1938    arg_types = {
1939        "expressions": False,
1940        "grouping_sets": False,
1941        "cube": False,
1942        "rollup": False,
1943        "totals": False,
1944        "all": False,
1945    }
1946
1947
1948class Lambda(Expression):
1949    arg_types = {"this": True, "expressions": True}
1950
1951
1952class Limit(Expression):
1953    arg_types = {"this": False, "expression": True, "offset": False, "expressions": False}
1954
1955
1956class Literal(Condition):
1957    arg_types = {"this": True, "is_string": True}
1958
1959    @property
1960    def hashable_args(self) -> t.Any:
1961        return (self.this, self.args.get("is_string"))
1962
1963    @classmethod
1964    def number(cls, number) -> Literal:
1965        return cls(this=str(number), is_string=False)
1966
1967    @classmethod
1968    def string(cls, string) -> Literal:
1969        return cls(this=str(string), is_string=True)
1970
1971    @property
1972    def output_name(self) -> str:
1973        return self.name
1974
1975
1976class Join(Expression):
1977    arg_types = {
1978        "this": True,
1979        "on": False,
1980        "side": False,
1981        "kind": False,
1982        "using": False,
1983        "method": False,
1984        "global": False,
1985        "hint": False,
1986    }
1987
1988    @property
1989    def method(self) -> str:
1990        return self.text("method").upper()
1991
1992    @property
1993    def kind(self) -> str:
1994        return self.text("kind").upper()
1995
1996    @property
1997    def side(self) -> str:
1998        return self.text("side").upper()
1999
2000    @property
2001    def hint(self) -> str:
2002        return self.text("hint").upper()
2003
2004    @property
2005    def alias_or_name(self) -> str:
2006        return self.this.alias_or_name
2007
2008    def on(
2009        self,
2010        *expressions: t.Optional[ExpOrStr],
2011        append: bool = True,
2012        dialect: DialectType = None,
2013        copy: bool = True,
2014        **opts,
2015    ) -> Join:
2016        """
2017        Append to or set the ON expressions.
2018
2019        Example:
2020            >>> import sqlglot
2021            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
2022            'JOIN x ON y = 1'
2023
2024        Args:
2025            *expressions: the SQL code strings to parse.
2026                If an `Expression` instance is passed, it will be used as-is.
2027                Multiple expressions are combined with an AND operator.
2028            append: if `True`, AND the new expressions to any existing expression.
2029                Otherwise, this resets the expression.
2030            dialect: the dialect used to parse the input expressions.
2031            copy: if `False`, modify this expression instance in-place.
2032            opts: other options to use to parse the input expressions.
2033
2034        Returns:
2035            The modified Join expression.
2036        """
2037        join = _apply_conjunction_builder(
2038            *expressions,
2039            instance=self,
2040            arg="on",
2041            append=append,
2042            dialect=dialect,
2043            copy=copy,
2044            **opts,
2045        )
2046
2047        if join.kind == "CROSS":
2048            join.set("kind", None)
2049
2050        return join
2051
2052    def using(
2053        self,
2054        *expressions: t.Optional[ExpOrStr],
2055        append: bool = True,
2056        dialect: DialectType = None,
2057        copy: bool = True,
2058        **opts,
2059    ) -> Join:
2060        """
2061        Append to or set the USING expressions.
2062
2063        Example:
2064            >>> import sqlglot
2065            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
2066            'JOIN x USING (foo, bla)'
2067
2068        Args:
2069            *expressions: the SQL code strings to parse.
2070                If an `Expression` instance is passed, it will be used as-is.
2071            append: if `True`, concatenate the new expressions to the existing "using" list.
2072                Otherwise, this resets the expression.
2073            dialect: the dialect used to parse the input expressions.
2074            copy: if `False`, modify this expression instance in-place.
2075            opts: other options to use to parse the input expressions.
2076
2077        Returns:
2078            The modified Join expression.
2079        """
2080        join = _apply_list_builder(
2081            *expressions,
2082            instance=self,
2083            arg="using",
2084            append=append,
2085            dialect=dialect,
2086            copy=copy,
2087            **opts,
2088        )
2089
2090        if join.kind == "CROSS":
2091            join.set("kind", None)
2092
2093        return join
2094
2095
2096class Lateral(UDTF):
2097    arg_types = {
2098        "this": True,
2099        "view": False,
2100        "outer": False,
2101        "alias": False,
2102        "cross_apply": False,  # True -> CROSS APPLY, False -> OUTER APPLY
2103    }
2104
2105
2106class MatchRecognize(Expression):
2107    arg_types = {
2108        "partition_by": False,
2109        "order": False,
2110        "measures": False,
2111        "rows": False,
2112        "after": False,
2113        "pattern": False,
2114        "define": False,
2115        "alias": False,
2116    }
2117
2118
2119# Clickhouse FROM FINAL modifier
2120# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier
2121class Final(Expression):
2122    pass
2123
2124
2125class Offset(Expression):
2126    arg_types = {"this": False, "expression": True, "expressions": False}
2127
2128
2129class Order(Expression):
2130    arg_types = {
2131        "this": False,
2132        "expressions": True,
2133        "interpolate": False,
2134        "siblings": False,
2135    }
2136
2137
2138# https://clickhouse.com/docs/en/sql-reference/statements/select/order-by#order-by-expr-with-fill-modifier
2139class WithFill(Expression):
2140    arg_types = {"from": False, "to": False, "step": False}
2141
2142
2143# hive specific sorts
2144# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
2145class Cluster(Order):
2146    pass
2147
2148
2149class Distribute(Order):
2150    pass
2151
2152
2153class Sort(Order):
2154    pass
2155
2156
2157class Ordered(Expression):
2158    arg_types = {"this": True, "desc": False, "nulls_first": True, "with_fill": False}
2159
2160
2161class Property(Expression):
2162    arg_types = {"this": True, "value": True}
2163
2164
2165class AlgorithmProperty(Property):
2166    arg_types = {"this": True}
2167
2168
2169class AutoIncrementProperty(Property):
2170    arg_types = {"this": True}
2171
2172
2173# https://docs.aws.amazon.com/prescriptive-guidance/latest/materialized-views-redshift/refreshing-materialized-views.html
2174class AutoRefreshProperty(Property):
2175    arg_types = {"this": True}
2176
2177
2178class BlockCompressionProperty(Property):
2179    arg_types = {
2180        "autotemp": False,
2181        "always": False,
2182        "default": False,
2183        "manual": False,
2184        "never": False,
2185    }
2186
2187
2188class CharacterSetProperty(Property):
2189    arg_types = {"this": True, "default": True}
2190
2191
2192class ChecksumProperty(Property):
2193    arg_types = {"on": False, "default": False}
2194
2195
2196class CollateProperty(Property):
2197    arg_types = {"this": True, "default": False}
2198
2199
2200class CopyGrantsProperty(Property):
2201    arg_types = {}
2202
2203
2204class DataBlocksizeProperty(Property):
2205    arg_types = {
2206        "size": False,
2207        "units": False,
2208        "minimum": False,
2209        "maximum": False,
2210        "default": False,
2211    }
2212
2213
2214class DefinerProperty(Property):
2215    arg_types = {"this": True}
2216
2217
2218class DistKeyProperty(Property):
2219    arg_types = {"this": True}
2220
2221
2222class DistStyleProperty(Property):
2223    arg_types = {"this": True}
2224
2225
2226class EngineProperty(Property):
2227    arg_types = {"this": True}
2228
2229
2230class HeapProperty(Property):
2231    arg_types = {}
2232
2233
2234class ToTableProperty(Property):
2235    arg_types = {"this": True}
2236
2237
2238class ExecuteAsProperty(Property):
2239    arg_types = {"this": True}
2240
2241
2242class ExternalProperty(Property):
2243    arg_types = {"this": False}
2244
2245
2246class FallbackProperty(Property):
2247    arg_types = {"no": True, "protection": False}
2248
2249
2250class FileFormatProperty(Property):
2251    arg_types = {"this": True}
2252
2253
2254class FreespaceProperty(Property):
2255    arg_types = {"this": True, "percent": False}
2256
2257
2258class InheritsProperty(Property):
2259    arg_types = {"expressions": True}
2260
2261
2262class InputModelProperty(Property):
2263    arg_types = {"this": True}
2264
2265
2266class OutputModelProperty(Property):
2267    arg_types = {"this": True}
2268
2269
2270class IsolatedLoadingProperty(Property):
2271    arg_types = {
2272        "no": False,
2273        "concurrent": False,
2274        "for_all": False,
2275        "for_insert": False,
2276        "for_none": False,
2277    }
2278
2279
2280class JournalProperty(Property):
2281    arg_types = {
2282        "no": False,
2283        "dual": False,
2284        "before": False,
2285        "local": False,
2286        "after": False,
2287    }
2288
2289
2290class LanguageProperty(Property):
2291    arg_types = {"this": True}
2292
2293
2294# spark ddl
2295class ClusteredByProperty(Property):
2296    arg_types = {"expressions": True, "sorted_by": False, "buckets": True}
2297
2298
2299class DictProperty(Property):
2300    arg_types = {"this": True, "kind": True, "settings": False}
2301
2302
2303class DictSubProperty(Property):
2304    pass
2305
2306
2307class DictRange(Property):
2308    arg_types = {"this": True, "min": True, "max": True}
2309
2310
2311# Clickhouse CREATE ... ON CLUSTER modifier
2312# https://clickhouse.com/docs/en/sql-reference/distributed-ddl
2313class OnCluster(Property):
2314    arg_types = {"this": True}
2315
2316
2317class LikeProperty(Property):
2318    arg_types = {"this": True, "expressions": False}
2319
2320
2321class LocationProperty(Property):
2322    arg_types = {"this": True}
2323
2324
2325class LockProperty(Property):
2326    arg_types = {"this": True}
2327
2328
2329class LockingProperty(Property):
2330    arg_types = {
2331        "this": False,
2332        "kind": True,
2333        "for_or_in": False,
2334        "lock_type": True,
2335        "override": False,
2336    }
2337
2338
2339class LogProperty(Property):
2340    arg_types = {"no": True}
2341
2342
2343class MaterializedProperty(Property):
2344    arg_types = {"this": False}
2345
2346
2347class MergeBlockRatioProperty(Property):
2348    arg_types = {"this": False, "no": False, "default": False, "percent": False}
2349
2350
2351class NoPrimaryIndexProperty(Property):
2352    arg_types = {}
2353
2354
2355class OnProperty(Property):
2356    arg_types = {"this": True}
2357
2358
2359class OnCommitProperty(Property):
2360    arg_types = {"delete": False}
2361
2362
2363class PartitionedByProperty(Property):
2364    arg_types = {"this": True}
2365
2366
2367# https://www.postgresql.org/docs/current/sql-createtable.html
2368class PartitionBoundSpec(Expression):
2369    # this -> IN / MODULUS, expression -> REMAINDER, from_expressions -> FROM (...), to_expressions -> TO (...)
2370    arg_types = {
2371        "this": False,
2372        "expression": False,
2373        "from_expressions": False,
2374        "to_expressions": False,
2375    }
2376
2377
2378class PartitionedOfProperty(Property):
2379    # this -> parent_table (schema), expression -> FOR VALUES ... / DEFAULT
2380    arg_types = {"this": True, "expression": True}
2381
2382
2383class RemoteWithConnectionModelProperty(Property):
2384    arg_types = {"this": True}
2385
2386
2387class ReturnsProperty(Property):
2388    arg_types = {"this": True, "is_table": False, "table": False}
2389
2390
2391class RowFormatProperty(Property):
2392    arg_types = {"this": True}
2393
2394
2395class RowFormatDelimitedProperty(Property):
2396    # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml
2397    arg_types = {
2398        "fields": False,
2399        "escaped": False,
2400        "collection_items": False,
2401        "map_keys": False,
2402        "lines": False,
2403        "null": False,
2404        "serde": False,
2405    }
2406
2407
2408class RowFormatSerdeProperty(Property):
2409    arg_types = {"this": True, "serde_properties": False}
2410
2411
2412# https://spark.apache.org/docs/3.1.2/sql-ref-syntax-qry-select-transform.html
2413class QueryTransform(Expression):
2414    arg_types = {
2415        "expressions": True,
2416        "command_script": True,
2417        "schema": False,
2418        "row_format_before": False,
2419        "record_writer": False,
2420        "row_format_after": False,
2421        "record_reader": False,
2422    }
2423
2424
2425class SampleProperty(Property):
2426    arg_types = {"this": True}
2427
2428
2429class SchemaCommentProperty(Property):
2430    arg_types = {"this": True}
2431
2432
2433class SerdeProperties(Property):
2434    arg_types = {"expressions": True}
2435
2436
2437class SetProperty(Property):
2438    arg_types = {"multi": True}
2439
2440
2441class SetConfigProperty(Property):
2442    arg_types = {"this": True}
2443
2444
2445class SettingsProperty(Property):
2446    arg_types = {"expressions": True}
2447
2448
2449class SortKeyProperty(Property):
2450    arg_types = {"this": True, "compound": False}
2451
2452
2453class SqlReadWriteProperty(Property):
2454    arg_types = {"this": True}
2455
2456
2457class SqlSecurityProperty(Property):
2458    arg_types = {"definer": True}
2459
2460
2461class StabilityProperty(Property):
2462    arg_types = {"this": True}
2463
2464
2465class TemporaryProperty(Property):
2466    arg_types = {"this": False}
2467
2468
2469class TransformModelProperty(Property):
2470    arg_types = {"expressions": True}
2471
2472
2473class TransientProperty(Property):
2474    arg_types = {"this": False}
2475
2476
2477class VolatileProperty(Property):
2478    arg_types = {"this": False}
2479
2480
2481class WithDataProperty(Property):
2482    arg_types = {"no": True, "statistics": False}
2483
2484
2485class WithJournalTableProperty(Property):
2486    arg_types = {"this": True}
2487
2488
2489class WithSystemVersioningProperty(Property):
2490    # this -> history table name, expression -> data consistency check
2491    arg_types = {"this": False, "expression": False}
2492
2493
2494class Properties(Expression):
2495    arg_types = {"expressions": True}
2496
2497    NAME_TO_PROPERTY = {
2498        "ALGORITHM": AlgorithmProperty,
2499        "AUTO_INCREMENT": AutoIncrementProperty,
2500        "CHARACTER SET": CharacterSetProperty,
2501        "CLUSTERED_BY": ClusteredByProperty,
2502        "COLLATE": CollateProperty,
2503        "COMMENT": SchemaCommentProperty,
2504        "DEFINER": DefinerProperty,
2505        "DISTKEY": DistKeyProperty,
2506        "DISTSTYLE": DistStyleProperty,
2507        "ENGINE": EngineProperty,
2508        "EXECUTE AS": ExecuteAsProperty,
2509        "FORMAT": FileFormatProperty,
2510        "LANGUAGE": LanguageProperty,
2511        "LOCATION": LocationProperty,
2512        "LOCK": LockProperty,
2513        "PARTITIONED_BY": PartitionedByProperty,
2514        "RETURNS": ReturnsProperty,
2515        "ROW_FORMAT": RowFormatProperty,
2516        "SORTKEY": SortKeyProperty,
2517    }
2518
2519    PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()}
2520
2521    # CREATE property locations
2522    # Form: schema specified
2523    #   create [POST_CREATE]
2524    #     table a [POST_NAME]
2525    #     (b int) [POST_SCHEMA]
2526    #     with ([POST_WITH])
2527    #     index (b) [POST_INDEX]
2528    #
2529    # Form: alias selection
2530    #   create [POST_CREATE]
2531    #     table a [POST_NAME]
2532    #     as [POST_ALIAS] (select * from b) [POST_EXPRESSION]
2533    #     index (c) [POST_INDEX]
2534    class Location(AutoName):
2535        POST_CREATE = auto()
2536        POST_NAME = auto()
2537        POST_SCHEMA = auto()
2538        POST_WITH = auto()
2539        POST_ALIAS = auto()
2540        POST_EXPRESSION = auto()
2541        POST_INDEX = auto()
2542        UNSUPPORTED = auto()
2543
2544    @classmethod
2545    def from_dict(cls, properties_dict: t.Dict) -> Properties:
2546        expressions = []
2547        for key, value in properties_dict.items():
2548            property_cls = cls.NAME_TO_PROPERTY.get(key.upper())
2549            if property_cls:
2550                expressions.append(property_cls(this=convert(value)))
2551            else:
2552                expressions.append(Property(this=Literal.string(key), value=convert(value)))
2553
2554        return cls(expressions=expressions)
2555
2556
2557class Qualify(Expression):
2558    pass
2559
2560
2561class InputOutputFormat(Expression):
2562    arg_types = {"input_format": False, "output_format": False}
2563
2564
2565# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql
2566class Return(Expression):
2567    pass
2568
2569
2570class Reference(Expression):
2571    arg_types = {"this": True, "expressions": False, "options": False}
2572
2573
2574class Tuple(Expression):
2575    arg_types = {"expressions": False}
2576
2577    def isin(
2578        self,
2579        *expressions: t.Any,
2580        query: t.Optional[ExpOrStr] = None,
2581        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
2582        copy: bool = True,
2583        **opts,
2584    ) -> In:
2585        return In(
2586            this=maybe_copy(self, copy),
2587            expressions=[convert(e, copy=copy) for e in expressions],
2588            query=maybe_parse(query, copy=copy, **opts) if query else None,
2589            unnest=(
2590                Unnest(
2591                    expressions=[
2592                        maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts)
2593                        for e in ensure_list(unnest)
2594                    ]
2595                )
2596                if unnest
2597                else None
2598            ),
2599        )
2600
2601
2602QUERY_MODIFIERS = {
2603    "match": False,
2604    "laterals": False,
2605    "joins": False,
2606    "connect": False,
2607    "pivots": False,
2608    "prewhere": False,
2609    "where": False,
2610    "group": False,
2611    "having": False,
2612    "qualify": False,
2613    "windows": False,
2614    "distribute": False,
2615    "sort": False,
2616    "cluster": False,
2617    "order": False,
2618    "limit": False,
2619    "offset": False,
2620    "locks": False,
2621    "sample": False,
2622    "settings": False,
2623    "format": False,
2624    "options": False,
2625}
2626
2627
2628# https://learn.microsoft.com/en-us/sql/t-sql/queries/option-clause-transact-sql?view=sql-server-ver16
2629# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-ver16
2630class QueryOption(Expression):
2631    arg_types = {"this": True, "expression": False}
2632
2633
2634# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver16
2635class WithTableHint(Expression):
2636    arg_types = {"expressions": True}
2637
2638
2639# https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
2640class IndexTableHint(Expression):
2641    arg_types = {"this": True, "expressions": False, "target": False}
2642
2643
2644# https://docs.snowflake.com/en/sql-reference/constructs/at-before
2645class HistoricalData(Expression):
2646    arg_types = {"this": True, "kind": True, "expression": True}
2647
2648
2649class Table(Expression):
2650    arg_types = {
2651        "this": False,
2652        "alias": False,
2653        "db": False,
2654        "catalog": False,
2655        "laterals": False,
2656        "joins": False,
2657        "pivots": False,
2658        "hints": False,
2659        "system_time": False,
2660        "version": False,
2661        "format": False,
2662        "pattern": False,
2663        "ordinality": False,
2664        "when": False,
2665        "only": False,
2666    }
2667
2668    @property
2669    def name(self) -> str:
2670        if isinstance(self.this, Func):
2671            return ""
2672        return self.this.name
2673
2674    @property
2675    def db(self) -> str:
2676        return self.text("db")
2677
2678    @property
2679    def catalog(self) -> str:
2680        return self.text("catalog")
2681
2682    @property
2683    def selects(self) -> t.List[Expression]:
2684        return []
2685
2686    @property
2687    def named_selects(self) -> t.List[str]:
2688        return []
2689
2690    @property
2691    def parts(self) -> t.List[Expression]:
2692        """Return the parts of a table in order catalog, db, table."""
2693        parts: t.List[Expression] = []
2694
2695        for arg in ("catalog", "db", "this"):
2696            part = self.args.get(arg)
2697
2698            if isinstance(part, Dot):
2699                parts.extend(part.flatten())
2700            elif isinstance(part, Expression):
2701                parts.append(part)
2702
2703        return parts
2704
2705    def to_column(self, copy: bool = True) -> Alias | Column | Dot:
2706        parts = self.parts
2707        col = column(*reversed(parts[0:4]), fields=parts[4:], copy=copy)  # type: ignore
2708        alias = self.args.get("alias")
2709        if alias:
2710            col = alias_(col, alias.this, copy=copy)
2711        return col
2712
2713
2714class Union(Query):
2715    arg_types = {
2716        "with": False,
2717        "this": True,
2718        "expression": True,
2719        "distinct": False,
2720        "by_name": False,
2721        **QUERY_MODIFIERS,
2722    }
2723
2724    def select(
2725        self,
2726        *expressions: t.Optional[ExpOrStr],
2727        append: bool = True,
2728        dialect: DialectType = None,
2729        copy: bool = True,
2730        **opts,
2731    ) -> Union:
2732        this = maybe_copy(self, copy)
2733        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
2734        this.expression.unnest().select(
2735            *expressions, append=append, dialect=dialect, copy=False, **opts
2736        )
2737        return this
2738
2739    @property
2740    def named_selects(self) -> t.List[str]:
2741        return self.this.unnest().named_selects
2742
2743    @property
2744    def is_star(self) -> bool:
2745        return self.this.is_star or self.expression.is_star
2746
2747    @property
2748    def selects(self) -> t.List[Expression]:
2749        return self.this.unnest().selects
2750
2751    @property
2752    def left(self) -> Expression:
2753        return self.this
2754
2755    @property
2756    def right(self) -> Expression:
2757        return self.expression
2758
2759
2760class Except(Union):
2761    pass
2762
2763
2764class Intersect(Union):
2765    pass
2766
2767
2768class Unnest(UDTF):
2769    arg_types = {
2770        "expressions": True,
2771        "alias": False,
2772        "offset": False,
2773    }
2774
2775    @property
2776    def selects(self) -> t.List[Expression]:
2777        columns = super().selects
2778        offset = self.args.get("offset")
2779        if offset:
2780            columns = columns + [to_identifier("offset") if offset is True else offset]
2781        return columns
2782
2783
2784class Update(Expression):
2785    arg_types = {
2786        "with": False,
2787        "this": False,
2788        "expressions": True,
2789        "from": False,
2790        "where": False,
2791        "returning": False,
2792        "order": False,
2793        "limit": False,
2794    }
2795
2796
2797class Values(UDTF):
2798    arg_types = {"expressions": True, "alias": False}
2799
2800
2801class Var(Expression):
2802    pass
2803
2804
2805class Version(Expression):
2806    """
2807    Time travel, iceberg, bigquery etc
2808    https://trino.io/docs/current/connector/iceberg.html?highlight=snapshot#using-snapshots
2809    https://www.databricks.com/blog/2019/02/04/introducing-delta-time-travel-for-large-scale-data-lakes.html
2810    https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#for_system_time_as_of
2811    https://learn.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-ver16
2812    this is either TIMESTAMP or VERSION
2813    kind is ("AS OF", "BETWEEN")
2814    """
2815
2816    arg_types = {"this": True, "kind": True, "expression": False}
2817
2818
2819class Schema(Expression):
2820    arg_types = {"this": False, "expressions": False}
2821
2822
2823# https://dev.mysql.com/doc/refman/8.0/en/select.html
2824# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html
2825class Lock(Expression):
2826    arg_types = {"update": True, "expressions": False, "wait": False}
2827
2828
2829class Select(Query):
2830    arg_types = {
2831        "with": False,
2832        "kind": False,
2833        "expressions": False,
2834        "hint": False,
2835        "distinct": False,
2836        "into": False,
2837        "from": False,
2838        **QUERY_MODIFIERS,
2839    }
2840
2841    def from_(
2842        self, expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
2843    ) -> Select:
2844        """
2845        Set the FROM expression.
2846
2847        Example:
2848            >>> Select().from_("tbl").select("x").sql()
2849            'SELECT x FROM tbl'
2850
2851        Args:
2852            expression : the SQL code strings to parse.
2853                If a `From` instance is passed, this is used as-is.
2854                If another `Expression` instance is passed, it will be wrapped in a `From`.
2855            dialect: the dialect used to parse the input expression.
2856            copy: if `False`, modify this expression instance in-place.
2857            opts: other options to use to parse the input expressions.
2858
2859        Returns:
2860            The modified Select expression.
2861        """
2862        return _apply_builder(
2863            expression=expression,
2864            instance=self,
2865            arg="from",
2866            into=From,
2867            prefix="FROM",
2868            dialect=dialect,
2869            copy=copy,
2870            **opts,
2871        )
2872
2873    def group_by(
2874        self,
2875        *expressions: t.Optional[ExpOrStr],
2876        append: bool = True,
2877        dialect: DialectType = None,
2878        copy: bool = True,
2879        **opts,
2880    ) -> Select:
2881        """
2882        Set the GROUP BY expression.
2883
2884        Example:
2885            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
2886            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
2887
2888        Args:
2889            *expressions: the SQL code strings to parse.
2890                If a `Group` instance is passed, this is used as-is.
2891                If another `Expression` instance is passed, it will be wrapped in a `Group`.
2892                If nothing is passed in then a group by is not applied to the expression
2893            append: if `True`, add to any existing expressions.
2894                Otherwise, this flattens all the `Group` expression into a single expression.
2895            dialect: the dialect used to parse the input expression.
2896            copy: if `False`, modify this expression instance in-place.
2897            opts: other options to use to parse the input expressions.
2898
2899        Returns:
2900            The modified Select expression.
2901        """
2902        if not expressions:
2903            return self if not copy else self.copy()
2904
2905        return _apply_child_list_builder(
2906            *expressions,
2907            instance=self,
2908            arg="group",
2909            append=append,
2910            copy=copy,
2911            prefix="GROUP BY",
2912            into=Group,
2913            dialect=dialect,
2914            **opts,
2915        )
2916
2917    def order_by(
2918        self,
2919        *expressions: t.Optional[ExpOrStr],
2920        append: bool = True,
2921        dialect: DialectType = None,
2922        copy: bool = True,
2923        **opts,
2924    ) -> Select:
2925        """
2926        Set the ORDER BY expression.
2927
2928        Example:
2929            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
2930            'SELECT x FROM tbl ORDER BY x DESC'
2931
2932        Args:
2933            *expressions: the SQL code strings to parse.
2934                If a `Group` instance is passed, this is used as-is.
2935                If another `Expression` instance is passed, it will be wrapped in a `Order`.
2936            append: if `True`, add to any existing expressions.
2937                Otherwise, this flattens all the `Order` expression into a single expression.
2938            dialect: the dialect used to parse the input expression.
2939            copy: if `False`, modify this expression instance in-place.
2940            opts: other options to use to parse the input expressions.
2941
2942        Returns:
2943            The modified Select expression.
2944        """
2945        return _apply_child_list_builder(
2946            *expressions,
2947            instance=self,
2948            arg="order",
2949            append=append,
2950            copy=copy,
2951            prefix="ORDER BY",
2952            into=Order,
2953            dialect=dialect,
2954            **opts,
2955        )
2956
2957    def sort_by(
2958        self,
2959        *expressions: t.Optional[ExpOrStr],
2960        append: bool = True,
2961        dialect: DialectType = None,
2962        copy: bool = True,
2963        **opts,
2964    ) -> Select:
2965        """
2966        Set the SORT BY expression.
2967
2968        Example:
2969            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql(dialect="hive")
2970            'SELECT x FROM tbl SORT BY x DESC'
2971
2972        Args:
2973            *expressions: the SQL code strings to parse.
2974                If a `Group` instance is passed, this is used as-is.
2975                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
2976            append: if `True`, add to any existing expressions.
2977                Otherwise, this flattens all the `Order` expression into a single expression.
2978            dialect: the dialect used to parse the input expression.
2979            copy: if `False`, modify this expression instance in-place.
2980            opts: other options to use to parse the input expressions.
2981
2982        Returns:
2983            The modified Select expression.
2984        """
2985        return _apply_child_list_builder(
2986            *expressions,
2987            instance=self,
2988            arg="sort",
2989            append=append,
2990            copy=copy,
2991            prefix="SORT BY",
2992            into=Sort,
2993            dialect=dialect,
2994            **opts,
2995        )
2996
2997    def cluster_by(
2998        self,
2999        *expressions: t.Optional[ExpOrStr],
3000        append: bool = True,
3001        dialect: DialectType = None,
3002        copy: bool = True,
3003        **opts,
3004    ) -> Select:
3005        """
3006        Set the CLUSTER BY expression.
3007
3008        Example:
3009            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql(dialect="hive")
3010            'SELECT x FROM tbl CLUSTER BY x DESC'
3011
3012        Args:
3013            *expressions: the SQL code strings to parse.
3014                If a `Group` instance is passed, this is used as-is.
3015                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
3016            append: if `True`, add to any existing expressions.
3017                Otherwise, this flattens all the `Order` expression into a single expression.
3018            dialect: the dialect used to parse the input expression.
3019            copy: if `False`, modify this expression instance in-place.
3020            opts: other options to use to parse the input expressions.
3021
3022        Returns:
3023            The modified Select expression.
3024        """
3025        return _apply_child_list_builder(
3026            *expressions,
3027            instance=self,
3028            arg="cluster",
3029            append=append,
3030            copy=copy,
3031            prefix="CLUSTER BY",
3032            into=Cluster,
3033            dialect=dialect,
3034            **opts,
3035        )
3036
3037    def limit(
3038        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
3039    ) -> Select:
3040        return _apply_builder(
3041            expression=expression,
3042            instance=self,
3043            arg="limit",
3044            into=Limit,
3045            prefix="LIMIT",
3046            dialect=dialect,
3047            copy=copy,
3048            into_arg="expression",
3049            **opts,
3050        )
3051
3052    def offset(
3053        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
3054    ) -> Select:
3055        """
3056        Set the OFFSET expression.
3057
3058        Example:
3059            >>> Select().from_("tbl").select("x").offset(10).sql()
3060            'SELECT x FROM tbl OFFSET 10'
3061
3062        Args:
3063            expression: the SQL code string to parse.
3064                This can also be an integer.
3065                If a `Offset` instance is passed, this is used as-is.
3066                If another `Expression` instance is passed, it will be wrapped in a `Offset`.
3067            dialect: the dialect used to parse the input expression.
3068            copy: if `False`, modify this expression instance in-place.
3069            opts: other options to use to parse the input expressions.
3070
3071        Returns:
3072            The modified Select expression.
3073        """
3074        return _apply_builder(
3075            expression=expression,
3076            instance=self,
3077            arg="offset",
3078            into=Offset,
3079            prefix="OFFSET",
3080            dialect=dialect,
3081            copy=copy,
3082            into_arg="expression",
3083            **opts,
3084        )
3085
3086    def select(
3087        self,
3088        *expressions: t.Optional[ExpOrStr],
3089        append: bool = True,
3090        dialect: DialectType = None,
3091        copy: bool = True,
3092        **opts,
3093    ) -> Select:
3094        return _apply_list_builder(
3095            *expressions,
3096            instance=self,
3097            arg="expressions",
3098            append=append,
3099            dialect=dialect,
3100            into=Expression,
3101            copy=copy,
3102            **opts,
3103        )
3104
3105    def lateral(
3106        self,
3107        *expressions: t.Optional[ExpOrStr],
3108        append: bool = True,
3109        dialect: DialectType = None,
3110        copy: bool = True,
3111        **opts,
3112    ) -> Select:
3113        """
3114        Append to or set the LATERAL expressions.
3115
3116        Example:
3117            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
3118            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
3119
3120        Args:
3121            *expressions: the SQL code strings to parse.
3122                If an `Expression` instance is passed, it will be used as-is.
3123            append: if `True`, add to any existing expressions.
3124                Otherwise, this resets the expressions.
3125            dialect: the dialect used to parse the input expressions.
3126            copy: if `False`, modify this expression instance in-place.
3127            opts: other options to use to parse the input expressions.
3128
3129        Returns:
3130            The modified Select expression.
3131        """
3132        return _apply_list_builder(
3133            *expressions,
3134            instance=self,
3135            arg="laterals",
3136            append=append,
3137            into=Lateral,
3138            prefix="LATERAL VIEW",
3139            dialect=dialect,
3140            copy=copy,
3141            **opts,
3142        )
3143
3144    def join(
3145        self,
3146        expression: ExpOrStr,
3147        on: t.Optional[ExpOrStr] = None,
3148        using: t.Optional[ExpOrStr | t.Collection[ExpOrStr]] = None,
3149        append: bool = True,
3150        join_type: t.Optional[str] = None,
3151        join_alias: t.Optional[Identifier | str] = None,
3152        dialect: DialectType = None,
3153        copy: bool = True,
3154        **opts,
3155    ) -> Select:
3156        """
3157        Append to or set the JOIN expressions.
3158
3159        Example:
3160            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
3161            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
3162
3163            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
3164            'SELECT 1 FROM a JOIN b USING (x, y, z)'
3165
3166            Use `join_type` to change the type of join:
3167
3168            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
3169            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
3170
3171        Args:
3172            expression: the SQL code string to parse.
3173                If an `Expression` instance is passed, it will be used as-is.
3174            on: optionally specify the join "on" criteria as a SQL string.
3175                If an `Expression` instance is passed, it will be used as-is.
3176            using: optionally specify the join "using" criteria as a SQL string.
3177                If an `Expression` instance is passed, it will be used as-is.
3178            append: if `True`, add to any existing expressions.
3179                Otherwise, this resets the expressions.
3180            join_type: if set, alter the parsed join type.
3181            join_alias: an optional alias for the joined source.
3182            dialect: the dialect used to parse the input expressions.
3183            copy: if `False`, modify this expression instance in-place.
3184            opts: other options to use to parse the input expressions.
3185
3186        Returns:
3187            Select: the modified expression.
3188        """
3189        parse_args: t.Dict[str, t.Any] = {"dialect": dialect, **opts}
3190
3191        try:
3192            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
3193        except ParseError:
3194            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
3195
3196        join = expression if isinstance(expression, Join) else Join(this=expression)
3197
3198        if isinstance(join.this, Select):
3199            join.this.replace(join.this.subquery())
3200
3201        if join_type:
3202            method: t.Optional[Token]
3203            side: t.Optional[Token]
3204            kind: t.Optional[Token]
3205
3206            method, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
3207
3208            if method:
3209                join.set("method", method.text)
3210            if side:
3211                join.set("side", side.text)
3212            if kind:
3213                join.set("kind", kind.text)
3214
3215        if on:
3216            on = and_(*ensure_list(on), dialect=dialect, copy=copy, **opts)
3217            join.set("on", on)
3218
3219        if using:
3220            join = _apply_list_builder(
3221                *ensure_list(using),
3222                instance=join,
3223                arg="using",
3224                append=append,
3225                copy=copy,
3226                into=Identifier,
3227                **opts,
3228            )
3229
3230        if join_alias:
3231            join.set("this", alias_(join.this, join_alias, table=True))
3232
3233        return _apply_list_builder(
3234            join,
3235            instance=self,
3236            arg="joins",
3237            append=append,
3238            copy=copy,
3239            **opts,
3240        )
3241
3242    def where(
3243        self,
3244        *expressions: t.Optional[ExpOrStr],
3245        append: bool = True,
3246        dialect: DialectType = None,
3247        copy: bool = True,
3248        **opts,
3249    ) -> Select:
3250        """
3251        Append to or set the WHERE expressions.
3252
3253        Example:
3254            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
3255            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
3256
3257        Args:
3258            *expressions: the SQL code strings to parse.
3259                If an `Expression` instance is passed, it will be used as-is.
3260                Multiple expressions are combined with an AND operator.
3261            append: if `True`, AND the new expressions to any existing expression.
3262                Otherwise, this resets the expression.
3263            dialect: the dialect used to parse the input expressions.
3264            copy: if `False`, modify this expression instance in-place.
3265            opts: other options to use to parse the input expressions.
3266
3267        Returns:
3268            Select: the modified expression.
3269        """
3270        return _apply_conjunction_builder(
3271            *expressions,
3272            instance=self,
3273            arg="where",
3274            append=append,
3275            into=Where,
3276            dialect=dialect,
3277            copy=copy,
3278            **opts,
3279        )
3280
3281    def having(
3282        self,
3283        *expressions: t.Optional[ExpOrStr],
3284        append: bool = True,
3285        dialect: DialectType = None,
3286        copy: bool = True,
3287        **opts,
3288    ) -> Select:
3289        """
3290        Append to or set the HAVING expressions.
3291
3292        Example:
3293            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
3294            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
3295
3296        Args:
3297            *expressions: the SQL code strings to parse.
3298                If an `Expression` instance is passed, it will be used as-is.
3299                Multiple expressions are combined with an AND operator.
3300            append: if `True`, AND the new expressions to any existing expression.
3301                Otherwise, this resets the expression.
3302            dialect: the dialect used to parse the input expressions.
3303            copy: if `False`, modify this expression instance in-place.
3304            opts: other options to use to parse the input expressions.
3305
3306        Returns:
3307            The modified Select expression.
3308        """
3309        return _apply_conjunction_builder(
3310            *expressions,
3311            instance=self,
3312            arg="having",
3313            append=append,
3314            into=Having,
3315            dialect=dialect,
3316            copy=copy,
3317            **opts,
3318        )
3319
3320    def window(
3321        self,
3322        *expressions: t.Optional[ExpOrStr],
3323        append: bool = True,
3324        dialect: DialectType = None,
3325        copy: bool = True,
3326        **opts,
3327    ) -> Select:
3328        return _apply_list_builder(
3329            *expressions,
3330            instance=self,
3331            arg="windows",
3332            append=append,
3333            into=Window,
3334            dialect=dialect,
3335            copy=copy,
3336            **opts,
3337        )
3338
3339    def qualify(
3340        self,
3341        *expressions: t.Optional[ExpOrStr],
3342        append: bool = True,
3343        dialect: DialectType = None,
3344        copy: bool = True,
3345        **opts,
3346    ) -> Select:
3347        return _apply_conjunction_builder(
3348            *expressions,
3349            instance=self,
3350            arg="qualify",
3351            append=append,
3352            into=Qualify,
3353            dialect=dialect,
3354            copy=copy,
3355            **opts,
3356        )
3357
3358    def distinct(
3359        self, *ons: t.Optional[ExpOrStr], distinct: bool = True, copy: bool = True
3360    ) -> Select:
3361        """
3362        Set the OFFSET expression.
3363
3364        Example:
3365            >>> Select().from_("tbl").select("x").distinct().sql()
3366            'SELECT DISTINCT x FROM tbl'
3367
3368        Args:
3369            ons: the expressions to distinct on
3370            distinct: whether the Select should be distinct
3371            copy: if `False`, modify this expression instance in-place.
3372
3373        Returns:
3374            Select: the modified expression.
3375        """
3376        instance = maybe_copy(self, copy)
3377        on = Tuple(expressions=[maybe_parse(on, copy=copy) for on in ons if on]) if ons else None
3378        instance.set("distinct", Distinct(on=on) if distinct else None)
3379        return instance
3380
3381    def ctas(
3382        self,
3383        table: ExpOrStr,
3384        properties: t.Optional[t.Dict] = None,
3385        dialect: DialectType = None,
3386        copy: bool = True,
3387        **opts,
3388    ) -> Create:
3389        """
3390        Convert this expression to a CREATE TABLE AS statement.
3391
3392        Example:
3393            >>> Select().select("*").from_("tbl").ctas("x").sql()
3394            'CREATE TABLE x AS SELECT * FROM tbl'
3395
3396        Args:
3397            table: the SQL code string to parse as the table name.
3398                If another `Expression` instance is passed, it will be used as-is.
3399            properties: an optional mapping of table properties
3400            dialect: the dialect used to parse the input table.
3401            copy: if `False`, modify this expression instance in-place.
3402            opts: other options to use to parse the input table.
3403
3404        Returns:
3405            The new Create expression.
3406        """
3407        instance = maybe_copy(self, copy)
3408        table_expression = maybe_parse(table, into=Table, dialect=dialect, **opts)
3409
3410        properties_expression = None
3411        if properties:
3412            properties_expression = Properties.from_dict(properties)
3413
3414        return Create(
3415            this=table_expression,
3416            kind="TABLE",
3417            expression=instance,
3418            properties=properties_expression,
3419        )
3420
3421    def lock(self, update: bool = True, copy: bool = True) -> Select:
3422        """
3423        Set the locking read mode for this expression.
3424
3425        Examples:
3426            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
3427            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
3428
3429            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
3430            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
3431
3432        Args:
3433            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
3434            copy: if `False`, modify this expression instance in-place.
3435
3436        Returns:
3437            The modified expression.
3438        """
3439        inst = maybe_copy(self, copy)
3440        inst.set("locks", [Lock(update=update)])
3441
3442        return inst
3443
3444    def hint(self, *hints: ExpOrStr, dialect: DialectType = None, copy: bool = True) -> Select:
3445        """
3446        Set hints for this expression.
3447
3448        Examples:
3449            >>> Select().select("x").from_("tbl").hint("BROADCAST(y)").sql(dialect="spark")
3450            'SELECT /*+ BROADCAST(y) */ x FROM tbl'
3451
3452        Args:
3453            hints: The SQL code strings to parse as the hints.
3454                If an `Expression` instance is passed, it will be used as-is.
3455            dialect: The dialect used to parse the hints.
3456            copy: If `False`, modify this expression instance in-place.
3457
3458        Returns:
3459            The modified expression.
3460        """
3461        inst = maybe_copy(self, copy)
3462        inst.set(
3463            "hint", Hint(expressions=[maybe_parse(h, copy=copy, dialect=dialect) for h in hints])
3464        )
3465
3466        return inst
3467
3468    @property
3469    def named_selects(self) -> t.List[str]:
3470        return [e.output_name for e in self.expressions if e.alias_or_name]
3471
3472    @property
3473    def is_star(self) -> bool:
3474        return any(expression.is_star for expression in self.expressions)
3475
3476    @property
3477    def selects(self) -> t.List[Expression]:
3478        return self.expressions
3479
3480
3481UNWRAPPED_QUERIES = (Select, Union)
3482
3483
3484class Subquery(DerivedTable, Query):
3485    arg_types = {
3486        "this": True,
3487        "alias": False,
3488        "with": False,
3489        **QUERY_MODIFIERS,
3490    }
3491
3492    def unnest(self):
3493        """Returns the first non subquery."""
3494        expression = self
3495        while isinstance(expression, Subquery):
3496            expression = expression.this
3497        return expression
3498
3499    def unwrap(self) -> Subquery:
3500        expression = self
3501        while expression.same_parent and expression.is_wrapper:
3502            expression = t.cast(Subquery, expression.parent)
3503        return expression
3504
3505    def select(
3506        self,
3507        *expressions: t.Optional[ExpOrStr],
3508        append: bool = True,
3509        dialect: DialectType = None,
3510        copy: bool = True,
3511        **opts,
3512    ) -> Subquery:
3513        this = maybe_copy(self, copy)
3514        this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
3515        return this
3516
3517    @property
3518    def is_wrapper(self) -> bool:
3519        """
3520        Whether this Subquery acts as a simple wrapper around another expression.
3521
3522        SELECT * FROM (((SELECT * FROM t)))
3523                      ^
3524                      This corresponds to a "wrapper" Subquery node
3525        """
3526        return all(v is None for k, v in self.args.items() if k != "this")
3527
3528    @property
3529    def is_star(self) -> bool:
3530        return self.this.is_star
3531
3532    @property
3533    def output_name(self) -> str:
3534        return self.alias
3535
3536
3537class TableSample(Expression):
3538    arg_types = {
3539        "this": False,
3540        "expressions": False,
3541        "method": False,
3542        "bucket_numerator": False,
3543        "bucket_denominator": False,
3544        "bucket_field": False,
3545        "percent": False,
3546        "rows": False,
3547        "size": False,
3548        "seed": False,
3549    }
3550
3551
3552class Tag(Expression):
3553    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
3554
3555    arg_types = {
3556        "this": False,
3557        "prefix": False,
3558        "postfix": False,
3559    }
3560
3561
3562# Represents both the standard SQL PIVOT operator and DuckDB's "simplified" PIVOT syntax
3563# https://duckdb.org/docs/sql/statements/pivot
3564class Pivot(Expression):
3565    arg_types = {
3566        "this": False,
3567        "alias": False,
3568        "expressions": False,
3569        "field": False,
3570        "unpivot": False,
3571        "using": False,
3572        "group": False,
3573        "columns": False,
3574        "include_nulls": False,
3575    }
3576
3577    @property
3578    def unpivot(self) -> bool:
3579        return bool(self.args.get("unpivot"))
3580
3581
3582class Window(Condition):
3583    arg_types = {
3584        "this": True,
3585        "partition_by": False,
3586        "order": False,
3587        "spec": False,
3588        "alias": False,
3589        "over": False,
3590        "first": False,
3591    }
3592
3593
3594class WindowSpec(Expression):
3595    arg_types = {
3596        "kind": False,
3597        "start": False,
3598        "start_side": False,
3599        "end": False,
3600        "end_side": False,
3601    }
3602
3603
3604class PreWhere(Expression):
3605    pass
3606
3607
3608class Where(Expression):
3609    pass
3610
3611
3612class Star(Expression):
3613    arg_types = {"except": False, "replace": False}
3614
3615    @property
3616    def name(self) -> str:
3617        return "*"
3618
3619    @property
3620    def output_name(self) -> str:
3621        return self.name
3622
3623
3624class Parameter(Condition):
3625    arg_types = {"this": True, "expression": False}
3626
3627
3628class SessionParameter(Condition):
3629    arg_types = {"this": True, "kind": False}
3630
3631
3632class Placeholder(Condition):
3633    arg_types = {"this": False, "kind": False}
3634
3635
3636class Null(Condition):
3637    arg_types: t.Dict[str, t.Any] = {}
3638
3639    @property
3640    def name(self) -> str:
3641        return "NULL"
3642
3643
3644class Boolean(Condition):
3645    pass
3646
3647
3648class DataTypeParam(Expression):
3649    arg_types = {"this": True, "expression": False}
3650
3651    @property
3652    def name(self) -> str:
3653        return self.this.name
3654
3655
3656class DataType(Expression):
3657    arg_types = {
3658        "this": True,
3659        "expressions": False,
3660        "nested": False,
3661        "values": False,
3662        "prefix": False,
3663        "kind": False,
3664    }
3665
3666    class Type(AutoName):
3667        ARRAY = auto()
3668        AGGREGATEFUNCTION = auto()
3669        SIMPLEAGGREGATEFUNCTION = auto()
3670        BIGDECIMAL = auto()
3671        BIGINT = auto()
3672        BIGSERIAL = auto()
3673        BINARY = auto()
3674        BIT = auto()
3675        BOOLEAN = auto()
3676        BPCHAR = auto()
3677        CHAR = auto()
3678        DATE = auto()
3679        DATE32 = auto()
3680        DATEMULTIRANGE = auto()
3681        DATERANGE = auto()
3682        DATETIME = auto()
3683        DATETIME64 = auto()
3684        DECIMAL = auto()
3685        DOUBLE = auto()
3686        ENUM = auto()
3687        ENUM8 = auto()
3688        ENUM16 = auto()
3689        FIXEDSTRING = auto()
3690        FLOAT = auto()
3691        GEOGRAPHY = auto()
3692        GEOMETRY = auto()
3693        HLLSKETCH = auto()
3694        HSTORE = auto()
3695        IMAGE = auto()
3696        INET = auto()
3697        INT = auto()
3698        INT128 = auto()
3699        INT256 = auto()
3700        INT4MULTIRANGE = auto()
3701        INT4RANGE = auto()
3702        INT8MULTIRANGE = auto()
3703        INT8RANGE = auto()
3704        INTERVAL = auto()
3705        IPADDRESS = auto()
3706        IPPREFIX = auto()
3707        IPV4 = auto()
3708        IPV6 = auto()
3709        JSON = auto()
3710        JSONB = auto()
3711        LONGBLOB = auto()
3712        LONGTEXT = auto()
3713        LOWCARDINALITY = auto()
3714        MAP = auto()
3715        MEDIUMBLOB = auto()
3716        MEDIUMINT = auto()
3717        MEDIUMTEXT = auto()
3718        MONEY = auto()
3719        NCHAR = auto()
3720        NESTED = auto()
3721        NULL = auto()
3722        NULLABLE = auto()
3723        NUMMULTIRANGE = auto()
3724        NUMRANGE = auto()
3725        NVARCHAR = auto()
3726        OBJECT = auto()
3727        ROWVERSION = auto()
3728        SERIAL = auto()
3729        SET = auto()
3730        SMALLINT = auto()
3731        SMALLMONEY = auto()
3732        SMALLSERIAL = auto()
3733        STRUCT = auto()
3734        SUPER = auto()
3735        TEXT = auto()
3736        TINYBLOB = auto()
3737        TINYTEXT = auto()
3738        TIME = auto()
3739        TIMETZ = auto()
3740        TIMESTAMP = auto()
3741        TIMESTAMPLTZ = auto()
3742        TIMESTAMPTZ = auto()
3743        TIMESTAMP_S = auto()
3744        TIMESTAMP_MS = auto()
3745        TIMESTAMP_NS = auto()
3746        TINYINT = auto()
3747        TSMULTIRANGE = auto()
3748        TSRANGE = auto()
3749        TSTZMULTIRANGE = auto()
3750        TSTZRANGE = auto()
3751        UBIGINT = auto()
3752        UINT = auto()
3753        UINT128 = auto()
3754        UINT256 = auto()
3755        UMEDIUMINT = auto()
3756        UDECIMAL = auto()
3757        UNIQUEIDENTIFIER = auto()
3758        UNKNOWN = auto()  # Sentinel value, useful for type annotation
3759        USERDEFINED = "USER-DEFINED"
3760        USMALLINT = auto()
3761        UTINYINT = auto()
3762        UUID = auto()
3763        VARBINARY = auto()
3764        VARCHAR = auto()
3765        VARIANT = auto()
3766        XML = auto()
3767        YEAR = auto()
3768
3769    TEXT_TYPES = {
3770        Type.CHAR,
3771        Type.NCHAR,
3772        Type.VARCHAR,
3773        Type.NVARCHAR,
3774        Type.TEXT,
3775    }
3776
3777    INTEGER_TYPES = {
3778        Type.INT,
3779        Type.TINYINT,
3780        Type.SMALLINT,
3781        Type.BIGINT,
3782        Type.INT128,
3783        Type.INT256,
3784        Type.BIT,
3785    }
3786
3787    FLOAT_TYPES = {
3788        Type.FLOAT,
3789        Type.DOUBLE,
3790    }
3791
3792    NUMERIC_TYPES = {
3793        *INTEGER_TYPES,
3794        *FLOAT_TYPES,
3795    }
3796
3797    TEMPORAL_TYPES = {
3798        Type.TIME,
3799        Type.TIMETZ,
3800        Type.TIMESTAMP,
3801        Type.TIMESTAMPTZ,
3802        Type.TIMESTAMPLTZ,
3803        Type.TIMESTAMP_S,
3804        Type.TIMESTAMP_MS,
3805        Type.TIMESTAMP_NS,
3806        Type.DATE,
3807        Type.DATE32,
3808        Type.DATETIME,
3809        Type.DATETIME64,
3810    }
3811
3812    @classmethod
3813    def build(
3814        cls,
3815        dtype: DATA_TYPE,
3816        dialect: DialectType = None,
3817        udt: bool = False,
3818        copy: bool = True,
3819        **kwargs,
3820    ) -> DataType:
3821        """
3822        Constructs a DataType object.
3823
3824        Args:
3825            dtype: the data type of interest.
3826            dialect: the dialect to use for parsing `dtype`, in case it's a string.
3827            udt: when set to True, `dtype` will be used as-is if it can't be parsed into a
3828                DataType, thus creating a user-defined type.
3829            copy: whether to copy the data type.
3830            kwargs: additional arguments to pass in the constructor of DataType.
3831
3832        Returns:
3833            The constructed DataType object.
3834        """
3835        from sqlglot import parse_one
3836
3837        if isinstance(dtype, str):
3838            if dtype.upper() == "UNKNOWN":
3839                return DataType(this=DataType.Type.UNKNOWN, **kwargs)
3840
3841            try:
3842                data_type_exp = parse_one(
3843                    dtype, read=dialect, into=DataType, error_level=ErrorLevel.IGNORE
3844                )
3845            except ParseError:
3846                if udt:
3847                    return DataType(this=DataType.Type.USERDEFINED, kind=dtype, **kwargs)
3848                raise
3849        elif isinstance(dtype, DataType.Type):
3850            data_type_exp = DataType(this=dtype)
3851        elif isinstance(dtype, DataType):
3852            return maybe_copy(dtype, copy)
3853        else:
3854            raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type")
3855
3856        return DataType(**{**data_type_exp.args, **kwargs})
3857
3858    def is_type(self, *dtypes: DATA_TYPE) -> bool:
3859        """
3860        Checks whether this DataType matches one of the provided data types. Nested types or precision
3861        will be compared using "structural equivalence" semantics, so e.g. array<int> != array<float>.
3862
3863        Args:
3864            dtypes: the data types to compare this DataType to.
3865
3866        Returns:
3867            True, if and only if there is a type in `dtypes` which is equal to this DataType.
3868        """
3869        for dtype in dtypes:
3870            other = DataType.build(dtype, copy=False, udt=True)
3871
3872            if (
3873                other.expressions
3874                or self.this == DataType.Type.USERDEFINED
3875                or other.this == DataType.Type.USERDEFINED
3876            ):
3877                matches = self == other
3878            else:
3879                matches = self.this == other.this
3880
3881            if matches:
3882                return True
3883        return False
3884
3885
3886DATA_TYPE = t.Union[str, DataType, DataType.Type]
3887
3888
3889# https://www.postgresql.org/docs/15/datatype-pseudo.html
3890class PseudoType(DataType):
3891    arg_types = {"this": True}
3892
3893
3894# https://www.postgresql.org/docs/15/datatype-oid.html
3895class ObjectIdentifier(DataType):
3896    arg_types = {"this": True}
3897
3898
3899# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
3900class SubqueryPredicate(Predicate):
3901    pass
3902
3903
3904class All(SubqueryPredicate):
3905    pass
3906
3907
3908class Any(SubqueryPredicate):
3909    pass
3910
3911
3912class Exists(SubqueryPredicate):
3913    pass
3914
3915
3916# Commands to interact with the databases or engines. For most of the command
3917# expressions we parse whatever comes after the command's name as a string.
3918class Command(Expression):
3919    arg_types = {"this": True, "expression": False}
3920
3921
3922class Transaction(Expression):
3923    arg_types = {"this": False, "modes": False, "mark": False}
3924
3925
3926class Commit(Expression):
3927    arg_types = {"chain": False, "this": False, "durability": False}
3928
3929
3930class Rollback(Expression):
3931    arg_types = {"savepoint": False, "this": False}
3932
3933
3934class AlterTable(Expression):
3935    arg_types = {
3936        "this": True,
3937        "actions": True,
3938        "exists": False,
3939        "only": False,
3940        "options": False,
3941    }
3942
3943
3944class AddConstraint(Expression):
3945    arg_types = {"expressions": True}
3946
3947
3948class DropPartition(Expression):
3949    arg_types = {"expressions": True, "exists": False}
3950
3951
3952# Binary expressions like (ADD a b)
3953class Binary(Condition):
3954    arg_types = {"this": True, "expression": True}
3955
3956    @property
3957    def left(self) -> Expression:
3958        return self.this
3959
3960    @property
3961    def right(self) -> Expression:
3962        return self.expression
3963
3964
3965class Add(Binary):
3966    pass
3967
3968
3969class Connector(Binary):
3970    pass
3971
3972
3973class And(Connector):
3974    pass
3975
3976
3977class Or(Connector):
3978    pass
3979
3980
3981class BitwiseAnd(Binary):
3982    pass
3983
3984
3985class BitwiseLeftShift(Binary):
3986    pass
3987
3988
3989class BitwiseOr(Binary):
3990    pass
3991
3992
3993class BitwiseRightShift(Binary):
3994    pass
3995
3996
3997class BitwiseXor(Binary):
3998    pass
3999
4000
4001class Div(Binary):
4002    arg_types = {"this": True, "expression": True, "typed": False, "safe": False}
4003
4004
4005class Overlaps(Binary):
4006    pass
4007
4008
4009class Dot(Binary):
4010    @property
4011    def name(self) -> str:
4012        return self.expression.name
4013
4014    @property
4015    def output_name(self) -> str:
4016        return self.name
4017
4018    @classmethod
4019    def build(self, expressions: t.Sequence[Expression]) -> Dot:
4020        """Build a Dot object with a sequence of expressions."""
4021        if len(expressions) < 2:
4022            raise ValueError("Dot requires >= 2 expressions.")
4023
4024        return t.cast(Dot, reduce(lambda x, y: Dot(this=x, expression=y), expressions))
4025
4026    @property
4027    def parts(self) -> t.List[Expression]:
4028        """Return the parts of a table / column in order catalog, db, table."""
4029        this, *parts = self.flatten()
4030
4031        parts.reverse()
4032
4033        for arg in ("this", "table", "db", "catalog"):
4034            part = this.args.get(arg)
4035
4036            if isinstance(part, Expression):
4037                parts.append(part)
4038
4039        parts.reverse()
4040        return parts
4041
4042
4043class DPipe(Binary):
4044    arg_types = {"this": True, "expression": True, "safe": False}
4045
4046
4047class EQ(Binary, Predicate):
4048    pass
4049
4050
4051class NullSafeEQ(Binary, Predicate):
4052    pass
4053
4054
4055class NullSafeNEQ(Binary, Predicate):
4056    pass
4057
4058
4059# Represents e.g. := in DuckDB which is mostly used for setting parameters
4060class PropertyEQ(Binary):
4061    pass
4062
4063
4064class Distance(Binary):
4065    pass
4066
4067
4068class Escape(Binary):
4069    pass
4070
4071
4072class Glob(Binary, Predicate):
4073    pass
4074
4075
4076class GT(Binary, Predicate):
4077    pass
4078
4079
4080class GTE(Binary, Predicate):
4081    pass
4082
4083
4084class ILike(Binary, Predicate):
4085    pass
4086
4087
4088class ILikeAny(Binary, Predicate):
4089    pass
4090
4091
4092class IntDiv(Binary):
4093    pass
4094
4095
4096class Is(Binary, Predicate):
4097    pass
4098
4099
4100class Kwarg(Binary):
4101    """Kwarg in special functions like func(kwarg => y)."""
4102
4103
4104class Like(Binary, Predicate):
4105    pass
4106
4107
4108class LikeAny(Binary, Predicate):
4109    pass
4110
4111
4112class LT(Binary, Predicate):
4113    pass
4114
4115
4116class LTE(Binary, Predicate):
4117    pass
4118
4119
4120class Mod(Binary):
4121    pass
4122
4123
4124class Mul(Binary):
4125    pass
4126
4127
4128class NEQ(Binary, Predicate):
4129    pass
4130
4131
4132# https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH
4133class Operator(Binary):
4134    arg_types = {"this": True, "operator": True, "expression": True}
4135
4136
4137class SimilarTo(Binary, Predicate):
4138    pass
4139
4140
4141class Slice(Binary):
4142    arg_types = {"this": False, "expression": False}
4143
4144
4145class Sub(Binary):
4146    pass
4147
4148
4149# Unary Expressions
4150# (NOT a)
4151class Unary(Condition):
4152    pass
4153
4154
4155class BitwiseNot(Unary):
4156    pass
4157
4158
4159class Not(Unary):
4160    pass
4161
4162
4163class Paren(Unary):
4164    arg_types = {"this": True, "with": False}
4165
4166    @property
4167    def output_name(self) -> str:
4168        return self.this.name
4169
4170
4171class Neg(Unary):
4172    pass
4173
4174
4175class Alias(Expression):
4176    arg_types = {"this": True, "alias": False}
4177
4178    @property
4179    def output_name(self) -> str:
4180        return self.alias
4181
4182
4183# BigQuery requires the UNPIVOT column list aliases to be either strings or ints, but
4184# other dialects require identifiers. This enables us to transpile between them easily.
4185class PivotAlias(Alias):
4186    pass
4187
4188
4189class Aliases(Expression):
4190    arg_types = {"this": True, "expressions": True}
4191
4192    @property
4193    def aliases(self):
4194        return self.expressions
4195
4196
4197# https://docs.aws.amazon.com/redshift/latest/dg/query-super.html
4198class AtIndex(Expression):
4199    arg_types = {"this": True, "expression": True}
4200
4201
4202class AtTimeZone(Expression):
4203    arg_types = {"this": True, "zone": True}
4204
4205
4206class FromTimeZone(Expression):
4207    arg_types = {"this": True, "zone": True}
4208
4209
4210class Between(Predicate):
4211    arg_types = {"this": True, "low": True, "high": True}
4212
4213
4214class Bracket(Condition):
4215    # https://cloud.google.com/bigquery/docs/reference/standard-sql/operators#array_subscript_operator
4216    arg_types = {"this": True, "expressions": True, "offset": False, "safe": False}
4217
4218    @property
4219    def output_name(self) -> str:
4220        if len(self.expressions) == 1:
4221            return self.expressions[0].output_name
4222
4223        return super().output_name
4224
4225
4226class Distinct(Expression):
4227    arg_types = {"expressions": False, "on": False}
4228
4229
4230class In(Predicate):
4231    arg_types = {
4232        "this": True,
4233        "expressions": False,
4234        "query": False,
4235        "unnest": False,
4236        "field": False,
4237        "is_global": False,
4238    }
4239
4240
4241# https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language#for-in
4242class ForIn(Expression):
4243    arg_types = {"this": True, "expression": True}
4244
4245
4246class TimeUnit(Expression):
4247    """Automatically converts unit arg into a var."""
4248
4249    arg_types = {"unit": False}
4250
4251    UNABBREVIATED_UNIT_NAME = {
4252        "D": "DAY",
4253        "H": "HOUR",
4254        "M": "MINUTE",
4255        "MS": "MILLISECOND",
4256        "NS": "NANOSECOND",
4257        "Q": "QUARTER",
4258        "S": "SECOND",
4259        "US": "MICROSECOND",
4260        "W": "WEEK",
4261        "Y": "YEAR",
4262    }
4263
4264    VAR_LIKE = (Column, Literal, Var)
4265
4266    def __init__(self, **args):
4267        unit = args.get("unit")
4268        if isinstance(unit, self.VAR_LIKE):
4269            args["unit"] = Var(
4270                this=(self.UNABBREVIATED_UNIT_NAME.get(unit.name) or unit.name).upper()
4271            )
4272        elif isinstance(unit, Week):
4273            unit.set("this", Var(this=unit.this.name.upper()))
4274
4275        super().__init__(**args)
4276
4277    @property
4278    def unit(self) -> t.Optional[Var]:
4279        return self.args.get("unit")
4280
4281
4282class IntervalOp(TimeUnit):
4283    arg_types = {"unit": True, "expression": True}
4284
4285    def interval(self):
4286        return Interval(
4287            this=self.expression.copy(),
4288            unit=self.unit.copy(),
4289        )
4290
4291
4292# https://www.oracletutorial.com/oracle-basics/oracle-interval/
4293# https://trino.io/docs/current/language/types.html#interval-day-to-second
4294# https://docs.databricks.com/en/sql/language-manual/data-types/interval-type.html
4295class IntervalSpan(DataType):
4296    arg_types = {"this": True, "expression": True}
4297
4298
4299class Interval(TimeUnit):
4300    arg_types = {"this": False, "unit": False}
4301
4302
4303class IgnoreNulls(Expression):
4304    pass
4305
4306
4307class RespectNulls(Expression):
4308    pass
4309
4310
4311# https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate-function-calls#max_min_clause
4312class HavingMax(Expression):
4313    arg_types = {"this": True, "expression": True, "max": True}
4314
4315
4316# Functions
4317class Func(Condition):
4318    """
4319    The base class for all function expressions.
4320
4321    Attributes:
4322        is_var_len_args (bool): if set to True the last argument defined in arg_types will be
4323            treated as a variable length argument and the argument's value will be stored as a list.
4324        _sql_names (list): the SQL name (1st item in the list) and aliases (subsequent items) for this
4325            function expression. These values are used to map this node to a name during parsing as
4326            well as to provide the function's name during SQL string generation. By default the SQL
4327            name is set to the expression's class name transformed to snake case.
4328    """
4329
4330    is_var_len_args = False
4331
4332    @classmethod
4333    def from_arg_list(cls, args):
4334        if cls.is_var_len_args:
4335            all_arg_keys = list(cls.arg_types)
4336            # If this function supports variable length argument treat the last argument as such.
4337            non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys
4338            num_non_var = len(non_var_len_arg_keys)
4339
4340            args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)}
4341            args_dict[all_arg_keys[-1]] = args[num_non_var:]
4342        else:
4343            args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)}
4344
4345        return cls(**args_dict)
4346
4347    @classmethod
4348    def sql_names(cls):
4349        if cls is Func:
4350            raise NotImplementedError(
4351                "SQL name is only supported by concrete function implementations"
4352            )
4353        if "_sql_names" not in cls.__dict__:
4354            cls._sql_names = [camel_to_snake_case(cls.__name__)]
4355        return cls._sql_names
4356
4357    @classmethod
4358    def sql_name(cls):
4359        return cls.sql_names()[0]
4360
4361    @classmethod
4362    def default_parser_mappings(cls):
4363        return {name: cls.from_arg_list for name in cls.sql_names()}
4364
4365
4366class AggFunc(Func):
4367    pass
4368
4369
4370class ParameterizedAgg(AggFunc):
4371    arg_types = {"this": True, "expressions": True, "params": True}
4372
4373
4374class Abs(Func):
4375    pass
4376
4377
4378class ArgMax(AggFunc):
4379    arg_types = {"this": True, "expression": True, "count": False}
4380    _sql_names = ["ARG_MAX", "ARGMAX", "MAX_BY"]
4381
4382
4383class ArgMin(AggFunc):
4384    arg_types = {"this": True, "expression": True, "count": False}
4385    _sql_names = ["ARG_MIN", "ARGMIN", "MIN_BY"]
4386
4387
4388class ApproxTopK(AggFunc):
4389    arg_types = {"this": True, "expression": False, "counters": False}
4390
4391
4392class Flatten(Func):
4393    pass
4394
4395
4396# https://spark.apache.org/docs/latest/api/sql/index.html#transform
4397class Transform(Func):
4398    arg_types = {"this": True, "expression": True}
4399
4400
4401class Anonymous(Func):
4402    arg_types = {"this": True, "expressions": False}
4403    is_var_len_args = True
4404
4405
4406class AnonymousAggFunc(AggFunc):
4407    arg_types = {"this": True, "expressions": False}
4408    is_var_len_args = True
4409
4410
4411# https://clickhouse.com/docs/en/sql-reference/aggregate-functions/combinators
4412class CombinedAggFunc(AnonymousAggFunc):
4413    arg_types = {"this": True, "expressions": False, "parts": True}
4414
4415
4416class CombinedParameterizedAgg(ParameterizedAgg):
4417    arg_types = {"this": True, "expressions": True, "params": True, "parts": True}
4418
4419
4420# https://docs.snowflake.com/en/sql-reference/functions/hll
4421# https://docs.aws.amazon.com/redshift/latest/dg/r_HLL_function.html
4422class Hll(AggFunc):
4423    arg_types = {"this": True, "expressions": False}
4424    is_var_len_args = True
4425
4426
4427class ApproxDistinct(AggFunc):
4428    arg_types = {"this": True, "accuracy": False}
4429    _sql_names = ["APPROX_DISTINCT", "APPROX_COUNT_DISTINCT"]
4430
4431
4432class Array(Func):
4433    arg_types = {"expressions": False}
4434    is_var_len_args = True
4435
4436
4437# https://docs.snowflake.com/en/sql-reference/functions/to_array
4438class ToArray(Func):
4439    pass
4440
4441
4442# https://docs.snowflake.com/en/sql-reference/functions/to_char
4443# https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/TO_CHAR-number.html
4444class ToChar(Func):
4445    arg_types = {"this": True, "format": False, "nlsparam": False}
4446
4447
4448# https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16#syntax
4449class Convert(Func):
4450    arg_types = {"this": True, "expression": True, "style": False}
4451
4452
4453class GenerateSeries(Func):
4454    arg_types = {"start": True, "end": True, "step": False, "is_end_exclusive": False}
4455
4456
4457class ArrayAgg(AggFunc):
4458    pass
4459
4460
4461class ArrayUniqueAgg(AggFunc):
4462    pass
4463
4464
4465class ArrayAll(Func):
4466    arg_types = {"this": True, "expression": True}
4467
4468
4469# Represents Python's `any(f(x) for x in array)`, where `array` is `this` and `f` is `expression`
4470class ArrayAny(Func):
4471    arg_types = {"this": True, "expression": True}
4472
4473
4474class ArrayConcat(Func):
4475    _sql_names = ["ARRAY_CONCAT", "ARRAY_CAT"]
4476    arg_types = {"this": True, "expressions": False}
4477    is_var_len_args = True
4478
4479
4480class ArrayContains(Binary, Func):
4481    pass
4482
4483
4484class ArrayContained(Binary):
4485    pass
4486
4487
4488class ArrayFilter(Func):
4489    arg_types = {"this": True, "expression": True}
4490    _sql_names = ["FILTER", "ARRAY_FILTER"]
4491
4492
4493class ArrayJoin(Func):
4494    arg_types = {"this": True, "expression": True, "null": False}
4495
4496
4497class ArrayOverlaps(Binary, Func):
4498    pass
4499
4500
4501class ArraySize(Func):
4502    arg_types = {"this": True, "expression": False}
4503    _sql_names = ["ARRAY_SIZE", "ARRAY_LENGTH"]
4504
4505
4506class ArraySort(Func):
4507    arg_types = {"this": True, "expression": False}
4508
4509
4510class ArraySum(Func):
4511    arg_types = {"this": True, "expression": False}
4512
4513
4514class ArrayUnionAgg(AggFunc):
4515    pass
4516
4517
4518class Avg(AggFunc):
4519    pass
4520
4521
4522class AnyValue(AggFunc):
4523    pass
4524
4525
4526class Lag(AggFunc):
4527    arg_types = {"this": True, "offset": False, "default": False}
4528
4529
4530class Lead(AggFunc):
4531    arg_types = {"this": True, "offset": False, "default": False}
4532
4533
4534# some dialects have a distinction between first and first_value, usually first is an aggregate func
4535# and first_value is a window func
4536class First(AggFunc):
4537    pass
4538
4539
4540class Last(AggFunc):
4541    pass
4542
4543
4544class FirstValue(AggFunc):
4545    pass
4546
4547
4548class LastValue(AggFunc):
4549    pass
4550
4551
4552class NthValue(AggFunc):
4553    arg_types = {"this": True, "offset": True}
4554
4555
4556class Case(Func):
4557    arg_types = {"this": False, "ifs": True, "default": False}
4558
4559    def when(self, condition: ExpOrStr, then: ExpOrStr, copy: bool = True, **opts) -> Case:
4560        instance = maybe_copy(self, copy)
4561        instance.append(
4562            "ifs",
4563            If(
4564                this=maybe_parse(condition, copy=copy, **opts),
4565                true=maybe_parse(then, copy=copy, **opts),
4566            ),
4567        )
4568        return instance
4569
4570    def else_(self, condition: ExpOrStr, copy: bool = True, **opts) -> Case:
4571        instance = maybe_copy(self, copy)
4572        instance.set("default", maybe_parse(condition, copy=copy, **opts))
4573        return instance
4574
4575
4576class Cast(Func):
4577    arg_types = {"this": True, "to": True, "format": False, "safe": False}
4578
4579    @property
4580    def name(self) -> str:
4581        return self.this.name
4582
4583    @property
4584    def to(self) -> DataType:
4585        return self.args["to"]
4586
4587    @property
4588    def output_name(self) -> str:
4589        return self.name
4590
4591    def is_type(self, *dtypes: DATA_TYPE) -> bool:
4592        """
4593        Checks whether this Cast's DataType matches one of the provided data types. Nested types
4594        like arrays or structs will be compared using "structural equivalence" semantics, so e.g.
4595        array<int> != array<float>.
4596
4597        Args:
4598            dtypes: the data types to compare this Cast's DataType to.
4599
4600        Returns:
4601            True, if and only if there is a type in `dtypes` which is equal to this Cast's DataType.
4602        """
4603        return self.to.is_type(*dtypes)
4604
4605
4606class TryCast(Cast):
4607    pass
4608
4609
4610class CastToStrType(Func):
4611    arg_types = {"this": True, "to": True}
4612
4613
4614class Collate(Binary, Func):
4615    pass
4616
4617
4618class Ceil(Func):
4619    arg_types = {"this": True, "decimals": False}
4620    _sql_names = ["CEIL", "CEILING"]
4621
4622
4623class Coalesce(Func):
4624    arg_types = {"this": True, "expressions": False}
4625    is_var_len_args = True
4626    _sql_names = ["COALESCE", "IFNULL", "NVL"]
4627
4628
4629class Chr(Func):
4630    arg_types = {"this": True, "charset": False, "expressions": False}
4631    is_var_len_args = True
4632    _sql_names = ["CHR", "CHAR"]
4633
4634
4635class Concat(Func):
4636    arg_types = {"expressions": True, "safe": False, "coalesce": False}
4637    is_var_len_args = True
4638
4639
4640class ConcatWs(Concat):
4641    _sql_names = ["CONCAT_WS"]
4642
4643
4644# https://docs.oracle.com/cd/B13789_01/server.101/b10759/operators004.htm#i1035022
4645class ConnectByRoot(Func):
4646    pass
4647
4648
4649class Count(AggFunc):
4650    arg_types = {"this": False, "expressions": False}
4651    is_var_len_args = True
4652
4653
4654class CountIf(AggFunc):
4655    _sql_names = ["COUNT_IF", "COUNTIF"]
4656
4657
4658# cube root
4659class Cbrt(Func):
4660    pass
4661
4662
4663class CurrentDate(Func):
4664    arg_types = {"this": False}
4665
4666
4667class CurrentDatetime(Func):
4668    arg_types = {"this": False}
4669
4670
4671class CurrentTime(Func):
4672    arg_types = {"this": False}
4673
4674
4675class CurrentTimestamp(Func):
4676    arg_types = {"this": False, "transaction": False}
4677
4678
4679class CurrentUser(Func):
4680    arg_types = {"this": False}
4681
4682
4683class DateAdd(Func, IntervalOp):
4684    arg_types = {"this": True, "expression": True, "unit": False}
4685
4686
4687class DateSub(Func, IntervalOp):
4688    arg_types = {"this": True, "expression": True, "unit": False}
4689
4690
4691class DateDiff(Func, TimeUnit):
4692    _sql_names = ["DATEDIFF", "DATE_DIFF"]
4693    arg_types = {"this": True, "expression": True, "unit": False}
4694
4695
4696class DateTrunc(Func):
4697    arg_types = {"unit": True, "this": True, "zone": False}
4698
4699    def __init__(self, **args):
4700        unit = args.get("unit")
4701        if isinstance(unit, TimeUnit.VAR_LIKE):
4702            args["unit"] = Literal.string(
4703                (TimeUnit.UNABBREVIATED_UNIT_NAME.get(unit.name) or unit.name).upper()
4704            )
4705        elif isinstance(unit, Week):
4706            unit.set("this", Literal.string(unit.this.name.upper()))
4707
4708        super().__init__(**args)
4709
4710    @property
4711    def unit(self) -> Expression:
4712        return self.args["unit"]
4713
4714
4715class DatetimeAdd(Func, IntervalOp):
4716    arg_types = {"this": True, "expression": True, "unit": False}
4717
4718
4719class DatetimeSub(Func, IntervalOp):
4720    arg_types = {"this": True, "expression": True, "unit": False}
4721
4722
4723class DatetimeDiff(Func, TimeUnit):
4724    arg_types = {"this": True, "expression": True, "unit": False}
4725
4726
4727class DatetimeTrunc(Func, TimeUnit):
4728    arg_types = {"this": True, "unit": True, "zone": False}
4729
4730
4731class DayOfWeek(Func):
4732    _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"]
4733
4734
4735class DayOfMonth(Func):
4736    _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"]
4737
4738
4739class DayOfYear(Func):
4740    _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"]
4741
4742
4743class ToDays(Func):
4744    pass
4745
4746
4747class WeekOfYear(Func):
4748    _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"]
4749
4750
4751class MonthsBetween(Func):
4752    arg_types = {"this": True, "expression": True, "roundoff": False}
4753
4754
4755class LastDay(Func, TimeUnit):
4756    _sql_names = ["LAST_DAY", "LAST_DAY_OF_MONTH"]
4757    arg_types = {"this": True, "unit": False}
4758
4759
4760class Extract(Func):
4761    arg_types = {"this": True, "expression": True}
4762
4763
4764class Timestamp(Func):
4765    arg_types = {"this": False, "expression": False, "with_tz": False}
4766
4767
4768class TimestampAdd(Func, TimeUnit):
4769    arg_types = {"this": True, "expression": True, "unit": False}
4770
4771
4772class TimestampSub(Func, TimeUnit):
4773    arg_types = {"this": True, "expression": True, "unit": False}
4774
4775
4776class TimestampDiff(Func, TimeUnit):
4777    _sql_names = ["TIMESTAMPDIFF", "TIMESTAMP_DIFF"]
4778    arg_types = {"this": True, "expression": True, "unit": False}
4779
4780
4781class TimestampTrunc(Func, TimeUnit):
4782    arg_types = {"this": True, "unit": True, "zone": False}
4783
4784
4785class TimeAdd(Func, TimeUnit):
4786    arg_types = {"this": True, "expression": True, "unit": False}
4787
4788
4789class TimeSub(Func, TimeUnit):
4790    arg_types = {"this": True, "expression": True, "unit": False}
4791
4792
4793class TimeDiff(Func, TimeUnit):
4794    arg_types = {"this": True, "expression": True, "unit": False}
4795
4796
4797class TimeTrunc(Func, TimeUnit):
4798    arg_types = {"this": True, "unit": True, "zone": False}
4799
4800
4801class DateFromParts(Func):
4802    _sql_names = ["DATE_FROM_PARTS", "DATEFROMPARTS"]
4803    arg_types = {"year": True, "month": True, "day": True}
4804
4805
4806class TimeFromParts(Func):
4807    _sql_names = ["TIME_FROM_PARTS", "TIMEFROMPARTS"]
4808    arg_types = {
4809        "hour": True,
4810        "min": True,
4811        "sec": True,
4812        "nano": False,
4813        "fractions": False,
4814        "precision": False,
4815    }
4816
4817
4818class DateStrToDate(Func):
4819    pass
4820
4821
4822class DateToDateStr(Func):
4823    pass
4824
4825
4826class DateToDi(Func):
4827    pass
4828
4829
4830# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date
4831class Date(Func):
4832    arg_types = {"this": False, "zone": False, "expressions": False}
4833    is_var_len_args = True
4834
4835
4836class Day(Func):
4837    pass
4838
4839
4840class Decode(Func):
4841    arg_types = {"this": True, "charset": True, "replace": False}
4842
4843
4844class DiToDate(Func):
4845    pass
4846
4847
4848class Encode(Func):
4849    arg_types = {"this": True, "charset": True}
4850
4851
4852class Exp(Func):
4853    pass
4854
4855
4856# https://docs.snowflake.com/en/sql-reference/functions/flatten
4857class Explode(Func):
4858    arg_types = {"this": True, "expressions": False}
4859    is_var_len_args = True
4860
4861
4862class ExplodeOuter(Explode):
4863    pass
4864
4865
4866class Posexplode(Explode):
4867    pass
4868
4869
4870class PosexplodeOuter(Posexplode, ExplodeOuter):
4871    pass
4872
4873
4874class Floor(Func):
4875    arg_types = {"this": True, "decimals": False}
4876
4877
4878class FromBase64(Func):
4879    pass
4880
4881
4882class ToBase64(Func):
4883    pass
4884
4885
4886class Greatest(Func):
4887    arg_types = {"this": True, "expressions": False}
4888    is_var_len_args = True
4889
4890
4891class GroupConcat(AggFunc):
4892    arg_types = {"this": True, "separator": False}
4893
4894
4895class Hex(Func):
4896    pass
4897
4898
4899class Xor(Connector, Func):
4900    arg_types = {"this": False, "expression": False, "expressions": False}
4901
4902
4903class If(Func):
4904    arg_types = {"this": True, "true": True, "false": False}
4905    _sql_names = ["IF", "IIF"]
4906
4907
4908class Nullif(Func):
4909    arg_types = {"this": True, "expression": True}
4910
4911
4912class Initcap(Func):
4913    arg_types = {"this": True, "expression": False}
4914
4915
4916class IsNan(Func):
4917    _sql_names = ["IS_NAN", "ISNAN"]
4918
4919
4920class IsInf(Func):
4921    _sql_names = ["IS_INF", "ISINF"]
4922
4923
4924class JSONPath(Expression):
4925    arg_types = {"expressions": True}
4926
4927    @property
4928    def output_name(self) -> str:
4929        last_segment = self.expressions[-1].this
4930        return last_segment if isinstance(last_segment, str) else ""
4931
4932
4933class JSONPathPart(Expression):
4934    arg_types = {}
4935
4936
4937class JSONPathFilter(JSONPathPart):
4938    arg_types = {"this": True}
4939
4940
4941class JSONPathKey(JSONPathPart):
4942    arg_types = {"this": True}
4943
4944
4945class JSONPathRecursive(JSONPathPart):
4946    arg_types = {"this": False}
4947
4948
4949class JSONPathRoot(JSONPathPart):
4950    pass
4951
4952
4953class JSONPathScript(JSONPathPart):
4954    arg_types = {"this": True}
4955
4956
4957class JSONPathSlice(JSONPathPart):
4958    arg_types = {"start": False, "end": False, "step": False}
4959
4960
4961class JSONPathSelector(JSONPathPart):
4962    arg_types = {"this": True}
4963
4964
4965class JSONPathSubscript(JSONPathPart):
4966    arg_types = {"this": True}
4967
4968
4969class JSONPathUnion(JSONPathPart):
4970    arg_types = {"expressions": True}
4971
4972
4973class JSONPathWildcard(JSONPathPart):
4974    pass
4975
4976
4977class FormatJson(Expression):
4978    pass
4979
4980
4981class JSONKeyValue(Expression):
4982    arg_types = {"this": True, "expression": True}
4983
4984
4985class JSONObject(Func):
4986    arg_types = {
4987        "expressions": False,
4988        "null_handling": False,
4989        "unique_keys": False,
4990        "return_type": False,
4991        "encoding": False,
4992    }
4993
4994
4995class JSONObjectAgg(AggFunc):
4996    arg_types = {
4997        "expressions": False,
4998        "null_handling": False,
4999        "unique_keys": False,
5000        "return_type": False,
5001        "encoding": False,
5002    }
5003
5004
5005# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAY.html
5006class JSONArray(Func):
5007    arg_types = {
5008        "expressions": True,
5009        "null_handling": False,
5010        "return_type": False,
5011        "strict": False,
5012    }
5013
5014
5015# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAYAGG.html
5016class JSONArrayAgg(Func):
5017    arg_types = {
5018        "this": True,
5019        "order": False,
5020        "null_handling": False,
5021        "return_type": False,
5022        "strict": False,
5023    }
5024
5025
5026# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
5027# Note: parsing of JSON column definitions is currently incomplete.
5028class JSONColumnDef(Expression):
5029    arg_types = {"this": False, "kind": False, "path": False, "nested_schema": False}
5030
5031
5032class JSONSchema(Expression):
5033    arg_types = {"expressions": True}
5034
5035
5036# # https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
5037class JSONTable(Func):
5038    arg_types = {
5039        "this": True,
5040        "schema": True,
5041        "path": False,
5042        "error_handling": False,
5043        "empty_handling": False,
5044    }
5045
5046
5047class OpenJSONColumnDef(Expression):
5048    arg_types = {"this": True, "kind": True, "path": False, "as_json": False}
5049
5050
5051class OpenJSON(Func):
5052    arg_types = {"this": True, "path": False, "expressions": False}
5053
5054
5055class JSONBContains(Binary):
5056    _sql_names = ["JSONB_CONTAINS"]
5057
5058
5059class JSONExtract(Binary, Func):
5060    arg_types = {"this": True, "expression": True, "only_json_types": False, "expressions": False}
5061    _sql_names = ["JSON_EXTRACT"]
5062    is_var_len_args = True
5063
5064    @property
5065    def output_name(self) -> str:
5066        return self.expression.output_name if not self.expressions else ""
5067
5068
5069class JSONExtractScalar(Binary, Func):
5070    arg_types = {"this": True, "expression": True, "only_json_types": False, "expressions": False}
5071    _sql_names = ["JSON_EXTRACT_SCALAR"]
5072    is_var_len_args = True
5073
5074    @property
5075    def output_name(self) -> str:
5076        return self.expression.output_name
5077
5078
5079class JSONBExtract(Binary, Func):
5080    _sql_names = ["JSONB_EXTRACT"]
5081
5082
5083class JSONBExtractScalar(Binary, Func):
5084    _sql_names = ["JSONB_EXTRACT_SCALAR"]
5085
5086
5087class JSONFormat(Func):
5088    arg_types = {"this": False, "options": False}
5089    _sql_names = ["JSON_FORMAT"]
5090
5091
5092# https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_member-of
5093class JSONArrayContains(Binary, Predicate, Func):
5094    _sql_names = ["JSON_ARRAY_CONTAINS"]
5095
5096
5097class ParseJSON(Func):
5098    # BigQuery, Snowflake have PARSE_JSON, Presto has JSON_PARSE
5099    _sql_names = ["PARSE_JSON", "JSON_PARSE"]
5100    arg_types = {"this": True, "expressions": False}
5101    is_var_len_args = True
5102
5103
5104class Least(Func):
5105    arg_types = {"this": True, "expressions": False}
5106    is_var_len_args = True
5107
5108
5109class Left(Func):
5110    arg_types = {"this": True, "expression": True}
5111
5112
5113class Right(Func):
5114    arg_types = {"this": True, "expression": True}
5115
5116
5117class Length(Func):
5118    _sql_names = ["LENGTH", "LEN"]
5119
5120
5121class Levenshtein(Func):
5122    arg_types = {
5123        "this": True,
5124        "expression": False,
5125        "ins_cost": False,
5126        "del_cost": False,
5127        "sub_cost": False,
5128    }
5129
5130
5131class Ln(Func):
5132    pass
5133
5134
5135class Log(Func):
5136    arg_types = {"this": True, "expression": False}
5137
5138
5139class Log2(Func):
5140    pass
5141
5142
5143class Log10(Func):
5144    pass
5145
5146
5147class LogicalOr(AggFunc):
5148    _sql_names = ["LOGICAL_OR", "BOOL_OR", "BOOLOR_AGG"]
5149
5150
5151class LogicalAnd(AggFunc):
5152    _sql_names = ["LOGICAL_AND", "BOOL_AND", "BOOLAND_AGG"]
5153
5154
5155class Lower(Func):
5156    _sql_names = ["LOWER", "LCASE"]
5157
5158
5159class Map(Func):
5160    arg_types = {"keys": False, "values": False}
5161
5162    @property
5163    def keys(self) -> t.List[Expression]:
5164        keys = self.args.get("keys")
5165        return keys.expressions if keys else []
5166
5167    @property
5168    def values(self) -> t.List[Expression]:
5169        values = self.args.get("values")
5170        return values.expressions if values else []
5171
5172
5173class MapFromEntries(Func):
5174    pass
5175
5176
5177class StarMap(Func):
5178    pass
5179
5180
5181class VarMap(Func):
5182    arg_types = {"keys": True, "values": True}
5183    is_var_len_args = True
5184
5185    @property
5186    def keys(self) -> t.List[Expression]:
5187        return self.args["keys"].expressions
5188
5189    @property
5190    def values(self) -> t.List[Expression]:
5191        return self.args["values"].expressions
5192
5193
5194# https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
5195class MatchAgainst(Func):
5196    arg_types = {"this": True, "expressions": True, "modifier": False}
5197
5198
5199class Max(AggFunc):
5200    arg_types = {"this": True, "expressions": False}
5201    is_var_len_args = True
5202
5203
5204class MD5(Func):
5205    _sql_names = ["MD5"]
5206
5207
5208# Represents the variant of the MD5 function that returns a binary value
5209class MD5Digest(Func):
5210    _sql_names = ["MD5_DIGEST"]
5211
5212
5213class Min(AggFunc):
5214    arg_types = {"this": True, "expressions": False}
5215    is_var_len_args = True
5216
5217
5218class Month(Func):
5219    pass
5220
5221
5222class AddMonths(Func):
5223    arg_types = {"this": True, "expression": True}
5224
5225
5226class Nvl2(Func):
5227    arg_types = {"this": True, "true": True, "false": False}
5228
5229
5230# https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-predict#mlpredict_function
5231class Predict(Func):
5232    arg_types = {"this": True, "expression": True, "params_struct": False}
5233
5234
5235class Pow(Binary, Func):
5236    _sql_names = ["POWER", "POW"]
5237
5238
5239class PercentileCont(AggFunc):
5240    arg_types = {"this": True, "expression": False}
5241
5242
5243class PercentileDisc(AggFunc):
5244    arg_types = {"this": True, "expression": False}
5245
5246
5247class Quantile(AggFunc):
5248    arg_types = {"this": True, "quantile": True}
5249
5250
5251class ApproxQuantile(Quantile):
5252    arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False}
5253
5254
5255class Rand(Func):
5256    _sql_names = ["RAND", "RANDOM"]
5257    arg_types = {"this": False}
5258
5259
5260class Randn(Func):
5261    arg_types = {"this": False}
5262
5263
5264class RangeN(Func):
5265    arg_types = {"this": True, "expressions": True, "each": False}
5266
5267
5268class ReadCSV(Func):
5269    _sql_names = ["READ_CSV"]
5270    is_var_len_args = True
5271    arg_types = {"this": True, "expressions": False}
5272
5273
5274class Reduce(Func):
5275    arg_types = {"this": True, "initial": True, "merge": True, "finish": False}
5276
5277
5278class RegexpExtract(Func):
5279    arg_types = {
5280        "this": True,
5281        "expression": True,
5282        "position": False,
5283        "occurrence": False,
5284        "parameters": False,
5285        "group": False,
5286    }
5287
5288
5289class RegexpReplace(Func):
5290    arg_types = {
5291        "this": True,
5292        "expression": True,
5293        "replacement": False,
5294        "position": False,
5295        "occurrence": False,
5296        "parameters": False,
5297        "modifiers": False,
5298    }
5299
5300
5301class RegexpLike(Binary, Func):
5302    arg_types = {"this": True, "expression": True, "flag": False}
5303
5304
5305class RegexpILike(Binary, Func):
5306    arg_types = {"this": True, "expression": True, "flag": False}
5307
5308
5309# https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.functions.split.html
5310# limit is the number of times a pattern is applied
5311class RegexpSplit(Func):
5312    arg_types = {"this": True, "expression": True, "limit": False}
5313
5314
5315class Repeat(Func):
5316    arg_types = {"this": True, "times": True}
5317
5318
5319# https://learn.microsoft.com/en-us/sql/t-sql/functions/round-transact-sql?view=sql-server-ver16
5320# tsql third argument function == trunctaion if not 0
5321class Round(Func):
5322    arg_types = {"this": True, "decimals": False, "truncate": False}
5323
5324
5325class RowNumber(Func):
5326    arg_types: t.Dict[str, t.Any] = {}
5327
5328
5329class SafeDivide(Func):
5330    arg_types = {"this": True, "expression": True}
5331
5332
5333class SHA(Func):
5334    _sql_names = ["SHA", "SHA1"]
5335
5336
5337class SHA2(Func):
5338    _sql_names = ["SHA2"]
5339    arg_types = {"this": True, "length": False}
5340
5341
5342class Sign(Func):
5343    _sql_names = ["SIGN", "SIGNUM"]
5344
5345
5346class SortArray(Func):
5347    arg_types = {"this": True, "asc": False}
5348
5349
5350class Split(Func):
5351    arg_types = {"this": True, "expression": True, "limit": False}
5352
5353
5354# Start may be omitted in the case of postgres
5355# https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6
5356class Substring(Func):
5357    arg_types = {"this": True, "start": False, "length": False}
5358
5359
5360class StandardHash(Func):
5361    arg_types = {"this": True, "expression": False}
5362
5363
5364class StartsWith(Func):
5365    _sql_names = ["STARTS_WITH", "STARTSWITH"]
5366    arg_types = {"this": True, "expression": True}
5367
5368
5369class StrPosition(Func):
5370    arg_types = {
5371        "this": True,
5372        "substr": True,
5373        "position": False,
5374        "instance": False,
5375    }
5376
5377
5378class StrToDate(Func):
5379    arg_types = {"this": True, "format": True}
5380
5381
5382class StrToTime(Func):
5383    arg_types = {"this": True, "format": True, "zone": False}
5384
5385
5386# Spark allows unix_timestamp()
5387# https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.unix_timestamp.html
5388class StrToUnix(Func):
5389    arg_types = {"this": False, "format": False}
5390
5391
5392# https://prestodb.io/docs/current/functions/string.html
5393# https://spark.apache.org/docs/latest/api/sql/index.html#str_to_map
5394class StrToMap(Func):
5395    arg_types = {
5396        "this": True,
5397        "pair_delim": False,
5398        "key_value_delim": False,
5399        "duplicate_resolution_callback": False,
5400    }
5401
5402
5403class NumberToStr(Func):
5404    arg_types = {"this": True, "format": True, "culture": False}
5405
5406
5407class FromBase(Func):
5408    arg_types = {"this": True, "expression": True}
5409
5410
5411class Struct(Func):
5412    arg_types = {"expressions": False}
5413    is_var_len_args = True
5414
5415
5416class StructExtract(Func):
5417    arg_types = {"this": True, "expression": True}
5418
5419
5420# https://learn.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql?view=sql-server-ver16
5421# https://docs.snowflake.com/en/sql-reference/functions/insert
5422class Stuff(Func):
5423    _sql_names = ["STUFF", "INSERT"]
5424    arg_types = {"this": True, "start": True, "length": True, "expression": True}
5425
5426
5427class Sum(AggFunc):
5428    pass
5429
5430
5431class Sqrt(Func):
5432    pass
5433
5434
5435class Stddev(AggFunc):
5436    pass
5437
5438
5439class StddevPop(AggFunc):
5440    pass
5441
5442
5443class StddevSamp(AggFunc):
5444    pass
5445
5446
5447class TimeToStr(Func):
5448    arg_types = {"this": True, "format": True, "culture": False}
5449
5450
5451class TimeToTimeStr(Func):
5452    pass
5453
5454
5455class TimeToUnix(Func):
5456    pass
5457
5458
5459class TimeStrToDate(Func):
5460    pass
5461
5462
5463class TimeStrToTime(Func):
5464    pass
5465
5466
5467class TimeStrToUnix(Func):
5468    pass
5469
5470
5471class Trim(Func):
5472    arg_types = {
5473        "this": True,
5474        "expression": False,
5475        "position": False,
5476        "collation": False,
5477    }
5478
5479
5480class TsOrDsAdd(Func, TimeUnit):
5481    # return_type is used to correctly cast the arguments of this expression when transpiling it
5482    arg_types = {"this": True, "expression": True, "unit": False, "return_type": False}
5483
5484    @property
5485    def return_type(self) -> DataType:
5486        return DataType.build(self.args.get("return_type") or DataType.Type.DATE)
5487
5488
5489class TsOrDsDiff(Func, TimeUnit):
5490    arg_types = {"this": True, "expression": True, "unit": False}
5491
5492
5493class TsOrDsToDateStr(Func):
5494    pass
5495
5496
5497class TsOrDsToDate(Func):
5498    arg_types = {"this": True, "format": False}
5499
5500
5501class TsOrDsToTime(Func):
5502    pass
5503
5504
5505class TsOrDiToDi(Func):
5506    pass
5507
5508
5509class Unhex(Func):
5510    pass
5511
5512
5513# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#unix_date
5514class UnixDate(Func):
5515    pass
5516
5517
5518class UnixToStr(Func):
5519    arg_types = {"this": True, "format": False}
5520
5521
5522# https://prestodb.io/docs/current/functions/datetime.html
5523# presto has weird zone/hours/minutes
5524class UnixToTime(Func):
5525    arg_types = {"this": True, "scale": False, "zone": False, "hours": False, "minutes": False}
5526
5527    SECONDS = Literal.number(0)
5528    DECIS = Literal.number(1)
5529    CENTIS = Literal.number(2)
5530    MILLIS = Literal.number(3)
5531    DECIMILLIS = Literal.number(4)
5532    CENTIMILLIS = Literal.number(5)
5533    MICROS = Literal.number(6)
5534    DECIMICROS = Literal.number(7)
5535    CENTIMICROS = Literal.number(8)
5536    NANOS = Literal.number(9)
5537
5538
5539class UnixToTimeStr(Func):
5540    pass
5541
5542
5543class TimestampFromParts(Func):
5544    _sql_names = ["TIMESTAMP_FROM_PARTS", "TIMESTAMPFROMPARTS"]
5545    arg_types = {
5546        "year": True,
5547        "month": True,
5548        "day": True,
5549        "hour": True,
5550        "min": True,
5551        "sec": True,
5552        "nano": False,
5553        "zone": False,
5554        "milli": False,
5555    }
5556
5557
5558class Upper(Func):
5559    _sql_names = ["UPPER", "UCASE"]
5560
5561
5562class Variance(AggFunc):
5563    _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"]
5564
5565
5566class VariancePop(AggFunc):
5567    _sql_names = ["VARIANCE_POP", "VAR_POP"]
5568
5569
5570class Week(Func):
5571    arg_types = {"this": True, "mode": False}
5572
5573
5574class XMLTable(Func):
5575    arg_types = {"this": True, "passing": False, "columns": False, "by_ref": False}
5576
5577
5578class Year(Func):
5579    pass
5580
5581
5582class Use(Expression):
5583    arg_types = {"this": True, "kind": False}
5584
5585
5586class Merge(Expression):
5587    arg_types = {
5588        "this": True,
5589        "using": True,
5590        "on": True,
5591        "expressions": True,
5592        "with": False,
5593    }
5594
5595
5596class When(Func):
5597    arg_types = {"matched": True, "source": False, "condition": False, "then": True}
5598
5599
5600# https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnextvaluefor.html
5601# https://learn.microsoft.com/en-us/sql/t-sql/functions/next-value-for-transact-sql?view=sql-server-ver16
5602class NextValueFor(Func):
5603    arg_types = {"this": True, "order": False}
5604
5605
5606def _norm_arg(arg):
5607    return arg.lower() if type(arg) is str else arg
5608
5609
5610ALL_FUNCTIONS = subclasses(__name__, Func, (AggFunc, Anonymous, Func))
5611FUNCTION_BY_NAME = {name: func for func in ALL_FUNCTIONS for name in func.sql_names()}
5612
5613JSON_PATH_PARTS = subclasses(__name__, JSONPathPart, (JSONPathPart,))
5614
5615
5616# Helpers
5617@t.overload
5618def maybe_parse(
5619    sql_or_expression: ExpOrStr,
5620    *,
5621    into: t.Type[E],
5622    dialect: DialectType = None,
5623    prefix: t.Optional[str] = None,
5624    copy: bool = False,
5625    **opts,
5626) -> E:
5627    ...
5628
5629
5630@t.overload
5631def maybe_parse(
5632    sql_or_expression: str | E,
5633    *,
5634    into: t.Optional[IntoType] = None,
5635    dialect: DialectType = None,
5636    prefix: t.Optional[str] = None,
5637    copy: bool = False,
5638    **opts,
5639) -> E:
5640    ...
5641
5642
5643def maybe_parse(
5644    sql_or_expression: ExpOrStr,
5645    *,
5646    into: t.Optional[IntoType] = None,
5647    dialect: DialectType = None,
5648    prefix: t.Optional[str] = None,
5649    copy: bool = False,
5650    **opts,
5651) -> Expression:
5652    """Gracefully handle a possible string or expression.
5653
5654    Example:
5655        >>> maybe_parse("1")
5656        Literal(this=1, is_string=False)
5657        >>> maybe_parse(to_identifier("x"))
5658        Identifier(this=x, quoted=False)
5659
5660    Args:
5661        sql_or_expression: the SQL code string or an expression
5662        into: the SQLGlot Expression to parse into
5663        dialect: the dialect used to parse the input expressions (in the case that an
5664            input expression is a SQL string).
5665        prefix: a string to prefix the sql with before it gets parsed
5666            (automatically includes a space)
5667        copy: whether to copy the expression.
5668        **opts: other options to use to parse the input expressions (again, in the case
5669            that an input expression is a SQL string).
5670
5671    Returns:
5672        Expression: the parsed or given expression.
5673    """
5674    if isinstance(sql_or_expression, Expression):
5675        if copy:
5676            return sql_or_expression.copy()
5677        return sql_or_expression
5678
5679    if sql_or_expression is None:
5680        raise ParseError("SQL cannot be None")
5681
5682    import sqlglot
5683
5684    sql = str(sql_or_expression)
5685    if prefix:
5686        sql = f"{prefix} {sql}"
5687
5688    return sqlglot.parse_one(sql, read=dialect, into=into, **opts)
5689
5690
5691@t.overload
5692def maybe_copy(instance: None, copy: bool = True) -> None:
5693    ...
5694
5695
5696@t.overload
5697def maybe_copy(instance: E, copy: bool = True) -> E:
5698    ...
5699
5700
5701def maybe_copy(instance, copy=True):
5702    return instance.copy() if copy and instance else instance
5703
5704
5705def _to_s(node: t.Any, verbose: bool = False, level: int = 0) -> str:
5706    """Generate a textual representation of an Expression tree"""
5707    indent = "\n" + ("  " * (level + 1))
5708    delim = f",{indent}"
5709
5710    if isinstance(node, Expression):
5711        args = {k: v for k, v in node.args.items() if (v is not None and v != []) or verbose}
5712
5713        if (node.type or verbose) and not isinstance(node, DataType):
5714            args["_type"] = node.type
5715        if node.comments or verbose:
5716            args["_comments"] = node.comments
5717
5718        if verbose:
5719            args["_id"] = id(node)
5720
5721        # Inline leaves for a more compact representation
5722        if node.is_leaf():
5723            indent = ""
5724            delim = ", "
5725
5726        items = delim.join([f"{k}={_to_s(v, verbose, level + 1)}" for k, v in args.items()])
5727        return f"{node.__class__.__name__}({indent}{items})"
5728
5729    if isinstance(node, list):
5730        items = delim.join(_to_s(i, verbose, level + 1) for i in node)
5731        items = f"{indent}{items}" if items else ""
5732        return f"[{items}]"
5733
5734    # Indent multiline strings to match the current level
5735    return indent.join(textwrap.dedent(str(node).strip("\n")).splitlines())
5736
5737
5738def _is_wrong_expression(expression, into):
5739    return isinstance(expression, Expression) and not isinstance(expression, into)
5740
5741
5742def _apply_builder(
5743    expression,
5744    instance,
5745    arg,
5746    copy=True,
5747    prefix=None,
5748    into=None,
5749    dialect=None,
5750    into_arg="this",
5751    **opts,
5752):
5753    if _is_wrong_expression(expression, into):
5754        expression = into(**{into_arg: expression})
5755    instance = maybe_copy(instance, copy)
5756    expression = maybe_parse(
5757        sql_or_expression=expression,
5758        prefix=prefix,
5759        into=into,
5760        dialect=dialect,
5761        **opts,
5762    )
5763    instance.set(arg, expression)
5764    return instance
5765
5766
5767def _apply_child_list_builder(
5768    *expressions,
5769    instance,
5770    arg,
5771    append=True,
5772    copy=True,
5773    prefix=None,
5774    into=None,
5775    dialect=None,
5776    properties=None,
5777    **opts,
5778):
5779    instance = maybe_copy(instance, copy)
5780    parsed = []
5781    for expression in expressions:
5782        if expression is not None:
5783            if _is_wrong_expression(expression, into):
5784                expression = into(expressions=[expression])
5785
5786            expression = maybe_parse(
5787                expression,
5788                into=into,
5789                dialect=dialect,
5790                prefix=prefix,
5791                **opts,
5792            )
5793            parsed.extend(expression.expressions)
5794
5795    existing = instance.args.get(arg)
5796    if append and existing:
5797        parsed = existing.expressions + parsed
5798
5799    child = into(expressions=parsed)
5800    for k, v in (properties or {}).items():
5801        child.set(k, v)
5802    instance.set(arg, child)
5803
5804    return instance
5805
5806
5807def _apply_list_builder(
5808    *expressions,
5809    instance,
5810    arg,
5811    append=True,
5812    copy=True,
5813    prefix=None,
5814    into=None,
5815    dialect=None,
5816    **opts,
5817):
5818    inst = maybe_copy(instance, copy)
5819
5820    expressions = [
5821        maybe_parse(
5822            sql_or_expression=expression,
5823            into=into,
5824            prefix=prefix,
5825            dialect=dialect,
5826            **opts,
5827        )
5828        for expression in expressions
5829        if expression is not None
5830    ]
5831
5832    existing_expressions = inst.args.get(arg)
5833    if append and existing_expressions:
5834        expressions = existing_expressions + expressions
5835
5836    inst.set(arg, expressions)
5837    return inst
5838
5839
5840def _apply_conjunction_builder(
5841    *expressions,
5842    instance,
5843    arg,
5844    into=None,
5845    append=True,
5846    copy=True,
5847    dialect=None,
5848    **opts,
5849):
5850    expressions = [exp for exp in expressions if exp is not None and exp != ""]
5851    if not expressions:
5852        return instance
5853
5854    inst = maybe_copy(instance, copy)
5855
5856    existing = inst.args.get(arg)
5857    if append and existing is not None:
5858        expressions = [existing.this if into else existing] + list(expressions)
5859
5860    node = and_(*expressions, dialect=dialect, copy=copy, **opts)
5861
5862    inst.set(arg, into(this=node) if into else node)
5863    return inst
5864
5865
5866def _apply_cte_builder(
5867    instance: E,
5868    alias: ExpOrStr,
5869    as_: ExpOrStr,
5870    recursive: t.Optional[bool] = None,
5871    append: bool = True,
5872    dialect: DialectType = None,
5873    copy: bool = True,
5874    **opts,
5875) -> E:
5876    alias_expression = maybe_parse(alias, dialect=dialect, into=TableAlias, **opts)
5877    as_expression = maybe_parse(as_, dialect=dialect, **opts)
5878    cte = CTE(this=as_expression, alias=alias_expression)
5879    return _apply_child_list_builder(
5880        cte,
5881        instance=instance,
5882        arg="with",
5883        append=append,
5884        copy=copy,
5885        into=With,
5886        properties={"recursive": recursive or False},
5887    )
5888
5889
5890def _combine(
5891    expressions: t.Sequence[t.Optional[ExpOrStr]],
5892    operator: t.Type[Connector],
5893    dialect: DialectType = None,
5894    copy: bool = True,
5895    **opts,
5896) -> Expression:
5897    conditions = [
5898        condition(expression, dialect=dialect, copy=copy, **opts)
5899        for expression in expressions
5900        if expression is not None
5901    ]
5902
5903    this, *rest = conditions
5904    if rest:
5905        this = _wrap(this, Connector)
5906    for expression in rest:
5907        this = operator(this=this, expression=_wrap(expression, Connector))
5908
5909    return this
5910
5911
5912def _wrap(expression: E, kind: t.Type[Expression]) -> E | Paren:
5913    return Paren(this=expression) if isinstance(expression, kind) else expression
5914
5915
5916def union(
5917    left: ExpOrStr,
5918    right: ExpOrStr,
5919    distinct: bool = True,
5920    dialect: DialectType = None,
5921    copy: bool = True,
5922    **opts,
5923) -> Union:
5924    """
5925    Initializes a syntax tree from one UNION expression.
5926
5927    Example:
5928        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
5929        'SELECT * FROM foo UNION SELECT * FROM bla'
5930
5931    Args:
5932        left: the SQL code string corresponding to the left-hand side.
5933            If an `Expression` instance is passed, it will be used as-is.
5934        right: the SQL code string corresponding to the right-hand side.
5935            If an `Expression` instance is passed, it will be used as-is.
5936        distinct: set the DISTINCT flag if and only if this is true.
5937        dialect: the dialect used to parse the input expression.
5938        copy: whether to copy the expression.
5939        opts: other options to use to parse the input expressions.
5940
5941    Returns:
5942        The new Union instance.
5943    """
5944    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
5945    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
5946
5947    return Union(this=left, expression=right, distinct=distinct)
5948
5949
5950def intersect(
5951    left: ExpOrStr,
5952    right: ExpOrStr,
5953    distinct: bool = True,
5954    dialect: DialectType = None,
5955    copy: bool = True,
5956    **opts,
5957) -> Intersect:
5958    """
5959    Initializes a syntax tree from one INTERSECT expression.
5960
5961    Example:
5962        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
5963        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
5964
5965    Args:
5966        left: the SQL code string corresponding to the left-hand side.
5967            If an `Expression` instance is passed, it will be used as-is.
5968        right: the SQL code string corresponding to the right-hand side.
5969            If an `Expression` instance is passed, it will be used as-is.
5970        distinct: set the DISTINCT flag if and only if this is true.
5971        dialect: the dialect used to parse the input expression.
5972        copy: whether to copy the expression.
5973        opts: other options to use to parse the input expressions.
5974
5975    Returns:
5976        The new Intersect instance.
5977    """
5978    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
5979    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
5980
5981    return Intersect(this=left, expression=right, distinct=distinct)
5982
5983
5984def except_(
5985    left: ExpOrStr,
5986    right: ExpOrStr,
5987    distinct: bool = True,
5988    dialect: DialectType = None,
5989    copy: bool = True,
5990    **opts,
5991) -> Except:
5992    """
5993    Initializes a syntax tree from one EXCEPT expression.
5994
5995    Example:
5996        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
5997        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
5998
5999    Args:
6000        left: the SQL code string corresponding to the left-hand side.
6001            If an `Expression` instance is passed, it will be used as-is.
6002        right: the SQL code string corresponding to the right-hand side.
6003            If an `Expression` instance is passed, it will be used as-is.
6004        distinct: set the DISTINCT flag if and only if this is true.
6005        dialect: the dialect used to parse the input expression.
6006        copy: whether to copy the expression.
6007        opts: other options to use to parse the input expressions.
6008
6009    Returns:
6010        The new Except instance.
6011    """
6012    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
6013    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
6014
6015    return Except(this=left, expression=right, distinct=distinct)
6016
6017
6018def select(*expressions: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
6019    """
6020    Initializes a syntax tree from one or multiple SELECT expressions.
6021
6022    Example:
6023        >>> select("col1", "col2").from_("tbl").sql()
6024        'SELECT col1, col2 FROM tbl'
6025
6026    Args:
6027        *expressions: the SQL code string to parse as the expressions of a
6028            SELECT statement. If an Expression instance is passed, this is used as-is.
6029        dialect: the dialect used to parse the input expressions (in the case that an
6030            input expression is a SQL string).
6031        **opts: other options to use to parse the input expressions (again, in the case
6032            that an input expression is a SQL string).
6033
6034    Returns:
6035        Select: the syntax tree for the SELECT statement.
6036    """
6037    return Select().select(*expressions, dialect=dialect, **opts)
6038
6039
6040def from_(expression: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
6041    """
6042    Initializes a syntax tree from a FROM expression.
6043
6044    Example:
6045        >>> from_("tbl").select("col1", "col2").sql()
6046        'SELECT col1, col2 FROM tbl'
6047
6048    Args:
6049        *expression: the SQL code string to parse as the FROM expressions of a
6050            SELECT statement. If an Expression instance is passed, this is used as-is.
6051        dialect: the dialect used to parse the input expression (in the case that the
6052            input expression is a SQL string).
6053        **opts: other options to use to parse the input expressions (again, in the case
6054            that the input expression is a SQL string).
6055
6056    Returns:
6057        Select: the syntax tree for the SELECT statement.
6058    """
6059    return Select().from_(expression, dialect=dialect, **opts)
6060
6061
6062def update(
6063    table: str | Table,
6064    properties: dict,
6065    where: t.Optional[ExpOrStr] = None,
6066    from_: t.Optional[ExpOrStr] = None,
6067    dialect: DialectType = None,
6068    **opts,
6069) -> Update:
6070    """
6071    Creates an update statement.
6072
6073    Example:
6074        >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql()
6075        "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1"
6076
6077    Args:
6078        *properties: dictionary of properties to set which are
6079            auto converted to sql objects eg None -> NULL
6080        where: sql conditional parsed into a WHERE statement
6081        from_: sql statement parsed into a FROM statement
6082        dialect: the dialect used to parse the input expressions.
6083        **opts: other options to use to parse the input expressions.
6084
6085    Returns:
6086        Update: the syntax tree for the UPDATE statement.
6087    """
6088    update_expr = Update(this=maybe_parse(table, into=Table, dialect=dialect))
6089    update_expr.set(
6090        "expressions",
6091        [
6092            EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v))
6093            for k, v in properties.items()
6094        ],
6095    )
6096    if from_:
6097        update_expr.set(
6098            "from",
6099            maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts),
6100        )
6101    if isinstance(where, Condition):
6102        where = Where(this=where)
6103    if where:
6104        update_expr.set(
6105            "where",
6106            maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
6107        )
6108    return update_expr
6109
6110
6111def delete(
6112    table: ExpOrStr,
6113    where: t.Optional[ExpOrStr] = None,
6114    returning: t.Optional[ExpOrStr] = None,
6115    dialect: DialectType = None,
6116    **opts,
6117) -> Delete:
6118    """
6119    Builds a delete statement.
6120
6121    Example:
6122        >>> delete("my_table", where="id > 1").sql()
6123        'DELETE FROM my_table WHERE id > 1'
6124
6125    Args:
6126        where: sql conditional parsed into a WHERE statement
6127        returning: sql conditional parsed into a RETURNING statement
6128        dialect: the dialect used to parse the input expressions.
6129        **opts: other options to use to parse the input expressions.
6130
6131    Returns:
6132        Delete: the syntax tree for the DELETE statement.
6133    """
6134    delete_expr = Delete().delete(table, dialect=dialect, copy=False, **opts)
6135    if where:
6136        delete_expr = delete_expr.where(where, dialect=dialect, copy=False, **opts)
6137    if returning:
6138        delete_expr = t.cast(
6139            Delete, delete_expr.returning(returning, dialect=dialect, copy=False, **opts)
6140        )
6141    return delete_expr
6142
6143
6144def insert(
6145    expression: ExpOrStr,
6146    into: ExpOrStr,
6147    columns: t.Optional[t.Sequence[str | Identifier]] = None,
6148    overwrite: t.Optional[bool] = None,
6149    returning: t.Optional[ExpOrStr] = None,
6150    dialect: DialectType = None,
6151    copy: bool = True,
6152    **opts,
6153) -> Insert:
6154    """
6155    Builds an INSERT statement.
6156
6157    Example:
6158        >>> insert("VALUES (1, 2, 3)", "tbl").sql()
6159        'INSERT INTO tbl VALUES (1, 2, 3)'
6160
6161    Args:
6162        expression: the sql string or expression of the INSERT statement
6163        into: the tbl to insert data to.
6164        columns: optionally the table's column names.
6165        overwrite: whether to INSERT OVERWRITE or not.
6166        returning: sql conditional parsed into a RETURNING statement
6167        dialect: the dialect used to parse the input expressions.
6168        copy: whether to copy the expression.
6169        **opts: other options to use to parse the input expressions.
6170
6171    Returns:
6172        Insert: the syntax tree for the INSERT statement.
6173    """
6174    expr = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
6175    this: Table | Schema = maybe_parse(into, into=Table, dialect=dialect, copy=copy, **opts)
6176
6177    if columns:
6178        this = Schema(this=this, expressions=[to_identifier(c, copy=copy) for c in columns])
6179
6180    insert = Insert(this=this, expression=expr, overwrite=overwrite)
6181
6182    if returning:
6183        insert = t.cast(Insert, insert.returning(returning, dialect=dialect, copy=False, **opts))
6184
6185    return insert
6186
6187
6188def condition(
6189    expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
6190) -> Condition:
6191    """
6192    Initialize a logical condition expression.
6193
6194    Example:
6195        >>> condition("x=1").sql()
6196        'x = 1'
6197
6198        This is helpful for composing larger logical syntax trees:
6199        >>> where = condition("x=1")
6200        >>> where = where.and_("y=1")
6201        >>> Select().from_("tbl").select("*").where(where).sql()
6202        'SELECT * FROM tbl WHERE x = 1 AND y = 1'
6203
6204    Args:
6205        *expression: the SQL code string to parse.
6206            If an Expression instance is passed, this is used as-is.
6207        dialect: the dialect used to parse the input expression (in the case that the
6208            input expression is a SQL string).
6209        copy: Whether to copy `expression` (only applies to expressions).
6210        **opts: other options to use to parse the input expressions (again, in the case
6211            that the input expression is a SQL string).
6212
6213    Returns:
6214        The new Condition instance
6215    """
6216    return maybe_parse(
6217        expression,
6218        into=Condition,
6219        dialect=dialect,
6220        copy=copy,
6221        **opts,
6222    )
6223
6224
6225def and_(
6226    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
6227) -> Condition:
6228    """
6229    Combine multiple conditions with an AND logical operator.
6230
6231    Example:
6232        >>> and_("x=1", and_("y=1", "z=1")).sql()
6233        'x = 1 AND (y = 1 AND z = 1)'
6234
6235    Args:
6236        *expressions: the SQL code strings to parse.
6237            If an Expression instance is passed, this is used as-is.
6238        dialect: the dialect used to parse the input expression.
6239        copy: whether to copy `expressions` (only applies to Expressions).
6240        **opts: other options to use to parse the input expressions.
6241
6242    Returns:
6243        And: the new condition
6244    """
6245    return t.cast(Condition, _combine(expressions, And, dialect, copy=copy, **opts))
6246
6247
6248def or_(
6249    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
6250) -> Condition:
6251    """
6252    Combine multiple conditions with an OR logical operator.
6253
6254    Example:
6255        >>> or_("x=1", or_("y=1", "z=1")).sql()
6256        'x = 1 OR (y = 1 OR z = 1)'
6257
6258    Args:
6259        *expressions: the SQL code strings to parse.
6260            If an Expression instance is passed, this is used as-is.
6261        dialect: the dialect used to parse the input expression.
6262        copy: whether to copy `expressions` (only applies to Expressions).
6263        **opts: other options to use to parse the input expressions.
6264
6265    Returns:
6266        Or: the new condition
6267    """
6268    return t.cast(Condition, _combine(expressions, Or, dialect, copy=copy, **opts))
6269
6270
6271def not_(expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts) -> Not:
6272    """
6273    Wrap a condition with a NOT operator.
6274
6275    Example:
6276        >>> not_("this_suit='black'").sql()
6277        "NOT this_suit = 'black'"
6278
6279    Args:
6280        expression: the SQL code string to parse.
6281            If an Expression instance is passed, this is used as-is.
6282        dialect: the dialect used to parse the input expression.
6283        copy: whether to copy the expression or not.
6284        **opts: other options to use to parse the input expressions.
6285
6286    Returns:
6287        The new condition.
6288    """
6289    this = condition(
6290        expression,
6291        dialect=dialect,
6292        copy=copy,
6293        **opts,
6294    )
6295    return Not(this=_wrap(this, Connector))
6296
6297
6298def paren(expression: ExpOrStr, copy: bool = True) -> Paren:
6299    """
6300    Wrap an expression in parentheses.
6301
6302    Example:
6303        >>> paren("5 + 3").sql()
6304        '(5 + 3)'
6305
6306    Args:
6307        expression: the SQL code string to parse.
6308            If an Expression instance is passed, this is used as-is.
6309        copy: whether to copy the expression or not.
6310
6311    Returns:
6312        The wrapped expression.
6313    """
6314    return Paren(this=maybe_parse(expression, copy=copy))
6315
6316
6317SAFE_IDENTIFIER_RE: t.Pattern[str] = re.compile(r"^[_a-zA-Z][\w]*$")
6318
6319
6320@t.overload
6321def to_identifier(name: None, quoted: t.Optional[bool] = None, copy: bool = True) -> None:
6322    ...
6323
6324
6325@t.overload
6326def to_identifier(
6327    name: str | Identifier, quoted: t.Optional[bool] = None, copy: bool = True
6328) -> Identifier:
6329    ...
6330
6331
6332def to_identifier(name, quoted=None, copy=True):
6333    """Builds an identifier.
6334
6335    Args:
6336        name: The name to turn into an identifier.
6337        quoted: Whether to force quote the identifier.
6338        copy: Whether to copy name if it's an Identifier.
6339
6340    Returns:
6341        The identifier ast node.
6342    """
6343
6344    if name is None:
6345        return None
6346
6347    if isinstance(name, Identifier):
6348        identifier = maybe_copy(name, copy)
6349    elif isinstance(name, str):
6350        identifier = Identifier(
6351            this=name,
6352            quoted=not SAFE_IDENTIFIER_RE.match(name) if quoted is None else quoted,
6353        )
6354    else:
6355        raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
6356    return identifier
6357
6358
6359def parse_identifier(name: str | Identifier, dialect: DialectType = None) -> Identifier:
6360    """
6361    Parses a given string into an identifier.
6362
6363    Args:
6364        name: The name to parse into an identifier.
6365        dialect: The dialect to parse against.
6366
6367    Returns:
6368        The identifier ast node.
6369    """
6370    try:
6371        expression = maybe_parse(name, dialect=dialect, into=Identifier)
6372    except ParseError:
6373        expression = to_identifier(name)
6374
6375    return expression
6376
6377
6378INTERVAL_STRING_RE = re.compile(r"\s*([0-9]+)\s*([a-zA-Z]+)\s*")
6379
6380
6381def to_interval(interval: str | Literal) -> Interval:
6382    """Builds an interval expression from a string like '1 day' or '5 months'."""
6383    if isinstance(interval, Literal):
6384        if not interval.is_string:
6385            raise ValueError("Invalid interval string.")
6386
6387        interval = interval.this
6388
6389    interval_parts = INTERVAL_STRING_RE.match(interval)  # type: ignore
6390
6391    if not interval_parts:
6392        raise ValueError("Invalid interval string.")
6393
6394    return Interval(
6395        this=Literal.string(interval_parts.group(1)),
6396        unit=Var(this=interval_parts.group(2).upper()),
6397    )
6398
6399
6400@t.overload
6401def to_table(sql_path: str | Table, **kwargs) -> Table:
6402    ...
6403
6404
6405@t.overload
6406def to_table(sql_path: None, **kwargs) -> None:
6407    ...
6408
6409
6410def to_table(
6411    sql_path: t.Optional[str | Table], dialect: DialectType = None, copy: bool = True, **kwargs
6412) -> t.Optional[Table]:
6413    """
6414    Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional.
6415    If a table is passed in then that table is returned.
6416
6417    Args:
6418        sql_path: a `[catalog].[schema].[table]` string.
6419        dialect: the source dialect according to which the table name will be parsed.
6420        copy: Whether to copy a table if it is passed in.
6421        kwargs: the kwargs to instantiate the resulting `Table` expression with.
6422
6423    Returns:
6424        A table expression.
6425    """
6426    if sql_path is None or isinstance(sql_path, Table):
6427        return maybe_copy(sql_path, copy=copy)
6428    if not isinstance(sql_path, str):
6429        raise ValueError(f"Invalid type provided for a table: {type(sql_path)}")
6430
6431    table = maybe_parse(sql_path, into=Table, dialect=dialect)
6432    if table:
6433        for k, v in kwargs.items():
6434            table.set(k, v)
6435
6436    return table
6437
6438
6439def to_column(sql_path: str | Column, **kwargs) -> Column:
6440    """
6441    Create a column from a `[table].[column]` sql path. Schema is optional.
6442
6443    If a column is passed in then that column is returned.
6444
6445    Args:
6446        sql_path: `[table].[column]` string
6447    Returns:
6448        Table: A column expression
6449    """
6450    if sql_path is None or isinstance(sql_path, Column):
6451        return sql_path
6452    if not isinstance(sql_path, str):
6453        raise ValueError(f"Invalid type provided for column: {type(sql_path)}")
6454    return column(*reversed(sql_path.split(".")), **kwargs)  # type: ignore
6455
6456
6457def alias_(
6458    expression: ExpOrStr,
6459    alias: t.Optional[str | Identifier],
6460    table: bool | t.Sequence[str | Identifier] = False,
6461    quoted: t.Optional[bool] = None,
6462    dialect: DialectType = None,
6463    copy: bool = True,
6464    **opts,
6465):
6466    """Create an Alias expression.
6467
6468    Example:
6469        >>> alias_('foo', 'bar').sql()
6470        'foo AS bar'
6471
6472        >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql()
6473        '(SELECT 1, 2) AS bar(a, b)'
6474
6475    Args:
6476        expression: the SQL code strings to parse.
6477            If an Expression instance is passed, this is used as-is.
6478        alias: the alias name to use. If the name has
6479            special characters it is quoted.
6480        table: Whether to create a table alias, can also be a list of columns.
6481        quoted: whether to quote the alias
6482        dialect: the dialect used to parse the input expression.
6483        copy: Whether to copy the expression.
6484        **opts: other options to use to parse the input expressions.
6485
6486    Returns:
6487        Alias: the aliased expression
6488    """
6489    exp = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
6490    alias = to_identifier(alias, quoted=quoted)
6491
6492    if table:
6493        table_alias = TableAlias(this=alias)
6494        exp.set("alias", table_alias)
6495
6496        if not isinstance(table, bool):
6497            for column in table:
6498                table_alias.append("columns", to_identifier(column, quoted=quoted))
6499
6500        return exp
6501
6502    # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in
6503    # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node
6504    # for the complete Window expression.
6505    #
6506    # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
6507
6508    if "alias" in exp.arg_types and not isinstance(exp, Window):
6509        exp.set("alias", alias)
6510        return exp
6511    return Alias(this=exp, alias=alias)
6512
6513
6514def subquery(
6515    expression: ExpOrStr,
6516    alias: t.Optional[Identifier | str] = None,
6517    dialect: DialectType = None,
6518    **opts,
6519) -> Select:
6520    """
6521    Build a subquery expression.
6522
6523    Example:
6524        >>> subquery('select x from tbl', 'bar').select('x').sql()
6525        'SELECT x FROM (SELECT x FROM tbl) AS bar'
6526
6527    Args:
6528        expression: the SQL code strings to parse.
6529            If an Expression instance is passed, this is used as-is.
6530        alias: the alias name to use.
6531        dialect: the dialect used to parse the input expression.
6532        **opts: other options to use to parse the input expressions.
6533
6534    Returns:
6535        A new Select instance with the subquery expression included.
6536    """
6537
6538    expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias)
6539    return Select().from_(expression, dialect=dialect, **opts)
6540
6541
6542@t.overload
6543def column(
6544    col: str | Identifier,
6545    table: t.Optional[str | Identifier] = None,
6546    db: t.Optional[str | Identifier] = None,
6547    catalog: t.Optional[str | Identifier] = None,
6548    *,
6549    fields: t.Collection[t.Union[str, Identifier]],
6550    quoted: t.Optional[bool] = None,
6551    copy: bool = True,
6552) -> Dot:
6553    pass
6554
6555
6556@t.overload
6557def column(
6558    col: str | Identifier,
6559    table: t.Optional[str | Identifier] = None,
6560    db: t.Optional[str | Identifier] = None,
6561    catalog: t.Optional[str | Identifier] = None,
6562    *,
6563    fields: Lit[None] = None,
6564    quoted: t.Optional[bool] = None,
6565    copy: bool = True,
6566) -> Column:
6567    pass
6568
6569
6570def column(
6571    col,
6572    table=None,
6573    db=None,
6574    catalog=None,
6575    *,
6576    fields=None,
6577    quoted=None,
6578    copy=True,
6579):
6580    """
6581    Build a Column.
6582
6583    Args:
6584        col: Column name.
6585        table: Table name.
6586        db: Database name.
6587        catalog: Catalog name.
6588        fields: Additional fields using dots.
6589        quoted: Whether to force quotes on the column's identifiers.
6590        copy: Whether to copy identifiers if passed in.
6591
6592    Returns:
6593        The new Column instance.
6594    """
6595    this = Column(
6596        this=to_identifier(col, quoted=quoted, copy=copy),
6597        table=to_identifier(table, quoted=quoted, copy=copy),
6598        db=to_identifier(db, quoted=quoted, copy=copy),
6599        catalog=to_identifier(catalog, quoted=quoted, copy=copy),
6600    )
6601
6602    if fields:
6603        this = Dot.build((this, *(to_identifier(field, copy=copy) for field in fields)))
6604    return this
6605
6606
6607def cast(expression: ExpOrStr, to: DATA_TYPE, copy: bool = True, **opts) -> Cast:
6608    """Cast an expression to a data type.
6609
6610    Example:
6611        >>> cast('x + 1', 'int').sql()
6612        'CAST(x + 1 AS INT)'
6613
6614    Args:
6615        expression: The expression to cast.
6616        to: The datatype to cast to.
6617        copy: Whether to copy the supplied expressions.
6618
6619    Returns:
6620        The new Cast instance.
6621    """
6622    expression = maybe_parse(expression, copy=copy, **opts)
6623    data_type = DataType.build(to, copy=copy, **opts)
6624    expression = Cast(this=expression, to=data_type)
6625    expression.type = data_type
6626    return expression
6627
6628
6629def table_(
6630    table: Identifier | str,
6631    db: t.Optional[Identifier | str] = None,
6632    catalog: t.Optional[Identifier | str] = None,
6633    quoted: t.Optional[bool] = None,
6634    alias: t.Optional[Identifier | str] = None,
6635) -> Table:
6636    """Build a Table.
6637
6638    Args:
6639        table: Table name.
6640        db: Database name.
6641        catalog: Catalog name.
6642        quote: Whether to force quotes on the table's identifiers.
6643        alias: Table's alias.
6644
6645    Returns:
6646        The new Table instance.
6647    """
6648    return Table(
6649        this=to_identifier(table, quoted=quoted) if table else None,
6650        db=to_identifier(db, quoted=quoted) if db else None,
6651        catalog=to_identifier(catalog, quoted=quoted) if catalog else None,
6652        alias=TableAlias(this=to_identifier(alias)) if alias else None,
6653    )
6654
6655
6656def values(
6657    values: t.Iterable[t.Tuple[t.Any, ...]],
6658    alias: t.Optional[str] = None,
6659    columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None,
6660) -> Values:
6661    """Build VALUES statement.
6662
6663    Example:
6664        >>> values([(1, '2')]).sql()
6665        "VALUES (1, '2')"
6666
6667    Args:
6668        values: values statements that will be converted to SQL
6669        alias: optional alias
6670        columns: Optional list of ordered column names or ordered dictionary of column names to types.
6671         If either are provided then an alias is also required.
6672
6673    Returns:
6674        Values: the Values expression object
6675    """
6676    if columns and not alias:
6677        raise ValueError("Alias is required when providing columns")
6678
6679    return Values(
6680        expressions=[convert(tup) for tup in values],
6681        alias=(
6682            TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns])
6683            if columns
6684            else (TableAlias(this=to_identifier(alias)) if alias else None)
6685        ),
6686    )
6687
6688
6689def var(name: t.Optional[ExpOrStr]) -> Var:
6690    """Build a SQL variable.
6691
6692    Example:
6693        >>> repr(var('x'))
6694        'Var(this=x)'
6695
6696        >>> repr(var(column('x', table='y')))
6697        'Var(this=x)'
6698
6699    Args:
6700        name: The name of the var or an expression who's name will become the var.
6701
6702    Returns:
6703        The new variable node.
6704    """
6705    if not name:
6706        raise ValueError("Cannot convert empty name into var.")
6707
6708    if isinstance(name, Expression):
6709        name = name.name
6710    return Var(this=name)
6711
6712
6713def rename_table(old_name: str | Table, new_name: str | Table) -> AlterTable:
6714    """Build ALTER TABLE... RENAME... expression
6715
6716    Args:
6717        old_name: The old name of the table
6718        new_name: The new name of the table
6719
6720    Returns:
6721        Alter table expression
6722    """
6723    old_table = to_table(old_name)
6724    new_table = to_table(new_name)
6725    return AlterTable(
6726        this=old_table,
6727        actions=[
6728            RenameTable(this=new_table),
6729        ],
6730    )
6731
6732
6733def rename_column(
6734    table_name: str | Table,
6735    old_column_name: str | Column,
6736    new_column_name: str | Column,
6737    exists: t.Optional[bool] = None,
6738) -> AlterTable:
6739    """Build ALTER TABLE... RENAME COLUMN... expression
6740
6741    Args:
6742        table_name: Name of the table
6743        old_column: The old name of the column
6744        new_column: The new name of the column
6745        exists: Whether to add the `IF EXISTS` clause
6746
6747    Returns:
6748        Alter table expression
6749    """
6750    table = to_table(table_name)
6751    old_column = to_column(old_column_name)
6752    new_column = to_column(new_column_name)
6753    return AlterTable(
6754        this=table,
6755        actions=[
6756            RenameColumn(this=old_column, to=new_column, exists=exists),
6757        ],
6758    )
6759
6760
6761def convert(value: t.Any, copy: bool = False) -> Expression:
6762    """Convert a python value into an expression object.
6763
6764    Raises an error if a conversion is not possible.
6765
6766    Args:
6767        value: A python object.
6768        copy: Whether to copy `value` (only applies to Expressions and collections).
6769
6770    Returns:
6771        Expression: the equivalent expression object.
6772    """
6773    if isinstance(value, Expression):
6774        return maybe_copy(value, copy)
6775    if isinstance(value, str):
6776        return Literal.string(value)
6777    if isinstance(value, bool):
6778        return Boolean(this=value)
6779    if value is None or (isinstance(value, float) and math.isnan(value)):
6780        return null()
6781    if isinstance(value, numbers.Number):
6782        return Literal.number(value)
6783    if isinstance(value, datetime.datetime):
6784        datetime_literal = Literal.string(
6785            (value if value.tzinfo else value.replace(tzinfo=datetime.timezone.utc)).isoformat()
6786        )
6787        return TimeStrToTime(this=datetime_literal)
6788    if isinstance(value, datetime.date):
6789        date_literal = Literal.string(value.strftime("%Y-%m-%d"))
6790        return DateStrToDate(this=date_literal)
6791    if isinstance(value, tuple):
6792        return Tuple(expressions=[convert(v, copy=copy) for v in value])
6793    if isinstance(value, list):
6794        return Array(expressions=[convert(v, copy=copy) for v in value])
6795    if isinstance(value, dict):
6796        return Map(
6797            keys=Array(expressions=[convert(k, copy=copy) for k in value]),
6798            values=Array(expressions=[convert(v, copy=copy) for v in value.values()]),
6799        )
6800    raise ValueError(f"Cannot convert {value}")
6801
6802
6803def replace_children(expression: Expression, fun: t.Callable, *args, **kwargs) -> None:
6804    """
6805    Replace children of an expression with the result of a lambda fun(child) -> exp.
6806    """
6807    for k, v in expression.args.items():
6808        is_list_arg = type(v) is list
6809
6810        child_nodes = v if is_list_arg else [v]
6811        new_child_nodes = []
6812
6813        for cn in child_nodes:
6814            if isinstance(cn, Expression):
6815                for child_node in ensure_collection(fun(cn, *args, **kwargs)):
6816                    new_child_nodes.append(child_node)
6817                    child_node.parent = expression
6818                    child_node.arg_key = k
6819            else:
6820                new_child_nodes.append(cn)
6821
6822        expression.args[k] = new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0)
6823
6824
6825def column_table_names(expression: Expression, exclude: str = "") -> t.Set[str]:
6826    """
6827    Return all table names referenced through columns in an expression.
6828
6829    Example:
6830        >>> import sqlglot
6831        >>> sorted(column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e")))
6832        ['a', 'c']
6833
6834    Args:
6835        expression: expression to find table names.
6836        exclude: a table name to exclude
6837
6838    Returns:
6839        A list of unique names.
6840    """
6841    return {
6842        table
6843        for table in (column.table for column in expression.find_all(Column))
6844        if table and table != exclude
6845    }
6846
6847
6848def table_name(table: Table | str, dialect: DialectType = None, identify: bool = False) -> str:
6849    """Get the full name of a table as a string.
6850
6851    Args:
6852        table: Table expression node or string.
6853        dialect: The dialect to generate the table name for.
6854        identify: Determines when an identifier should be quoted. Possible values are:
6855            False (default): Never quote, except in cases where it's mandatory by the dialect.
6856            True: Always quote.
6857
6858    Examples:
6859        >>> from sqlglot import exp, parse_one
6860        >>> table_name(parse_one("select * from a.b.c").find(exp.Table))
6861        'a.b.c'
6862
6863    Returns:
6864        The table name.
6865    """
6866
6867    table = maybe_parse(table, into=Table, dialect=dialect)
6868
6869    if not table:
6870        raise ValueError(f"Cannot parse {table}")
6871
6872    return ".".join(
6873        (
6874            part.sql(dialect=dialect, identify=True, copy=False)
6875            if identify or not SAFE_IDENTIFIER_RE.match(part.name)
6876            else part.name
6877        )
6878        for part in table.parts
6879    )
6880
6881
6882def normalize_table_name(table: str | Table, dialect: DialectType = None, copy: bool = True) -> str:
6883    """Returns a case normalized table name without quotes.
6884
6885    Args:
6886        table: the table to normalize
6887        dialect: the dialect to use for normalization rules
6888        copy: whether to copy the expression.
6889
6890    Examples:
6891        >>> normalize_table_name("`A-B`.c", dialect="bigquery")
6892        'A-B.c'
6893    """
6894    from sqlglot.optimizer.normalize_identifiers import normalize_identifiers
6895
6896    return ".".join(
6897        p.name
6898        for p in normalize_identifiers(
6899            to_table(table, dialect=dialect, copy=copy), dialect=dialect
6900        ).parts
6901    )
6902
6903
6904def replace_tables(
6905    expression: E, mapping: t.Dict[str, str], dialect: DialectType = None, copy: bool = True
6906) -> E:
6907    """Replace all tables in expression according to the mapping.
6908
6909    Args:
6910        expression: expression node to be transformed and replaced.
6911        mapping: mapping of table names.
6912        dialect: the dialect of the mapping table
6913        copy: whether to copy the expression.
6914
6915    Examples:
6916        >>> from sqlglot import exp, parse_one
6917        >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql()
6918        'SELECT * FROM c /* a.b */'
6919
6920    Returns:
6921        The mapped expression.
6922    """
6923
6924    mapping = {normalize_table_name(k, dialect=dialect): v for k, v in mapping.items()}
6925
6926    def _replace_tables(node: Expression) -> Expression:
6927        if isinstance(node, Table):
6928            original = normalize_table_name(node, dialect=dialect)
6929            new_name = mapping.get(original)
6930
6931            if new_name:
6932                table = to_table(
6933                    new_name,
6934                    **{k: v for k, v in node.args.items() if k not in TABLE_PARTS},
6935                    dialect=dialect,
6936                )
6937                table.add_comments([original])
6938                return table
6939        return node
6940
6941    return expression.transform(_replace_tables, copy=copy)
6942
6943
6944def replace_placeholders(expression: Expression, *args, **kwargs) -> Expression:
6945    """Replace placeholders in an expression.
6946
6947    Args:
6948        expression: expression node to be transformed and replaced.
6949        args: positional names that will substitute unnamed placeholders in the given order.
6950        kwargs: keyword arguments that will substitute named placeholders.
6951
6952    Examples:
6953        >>> from sqlglot import exp, parse_one
6954        >>> replace_placeholders(
6955        ...     parse_one("select * from :tbl where ? = ?"),
6956        ...     exp.to_identifier("str_col"), "b", tbl=exp.to_identifier("foo")
6957        ... ).sql()
6958        "SELECT * FROM foo WHERE str_col = 'b'"
6959
6960    Returns:
6961        The mapped expression.
6962    """
6963
6964    def _replace_placeholders(node: Expression, args, **kwargs) -> Expression:
6965        if isinstance(node, Placeholder):
6966            if node.name:
6967                new_name = kwargs.get(node.name)
6968                if new_name is not None:
6969                    return convert(new_name)
6970            else:
6971                try:
6972                    return convert(next(args))
6973                except StopIteration:
6974                    pass
6975        return node
6976
6977    return expression.transform(_replace_placeholders, iter(args), **kwargs)
6978
6979
6980def expand(
6981    expression: Expression,
6982    sources: t.Dict[str, Query],
6983    dialect: DialectType = None,
6984    copy: bool = True,
6985) -> Expression:
6986    """Transforms an expression by expanding all referenced sources into subqueries.
6987
6988    Examples:
6989        >>> from sqlglot import parse_one
6990        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql()
6991        'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
6992
6993        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y"), "y": parse_one("select * from z")}).sql()
6994        'SELECT * FROM (SELECT * FROM (SELECT * FROM z) AS y /* source: y */) AS z /* source: x */'
6995
6996    Args:
6997        expression: The expression to expand.
6998        sources: A dictionary of name to Queries.
6999        dialect: The dialect of the sources dict.
7000        copy: Whether to copy the expression during transformation. Defaults to True.
7001
7002    Returns:
7003        The transformed expression.
7004    """
7005    sources = {normalize_table_name(k, dialect=dialect): v for k, v in sources.items()}
7006
7007    def _expand(node: Expression):
7008        if isinstance(node, Table):
7009            name = normalize_table_name(node, dialect=dialect)
7010            source = sources.get(name)
7011            if source:
7012                subquery = source.subquery(node.alias or name)
7013                subquery.comments = [f"source: {name}"]
7014                return subquery.transform(_expand, copy=False)
7015        return node
7016
7017    return expression.transform(_expand, copy=copy)
7018
7019
7020def func(name: str, *args, copy: bool = True, dialect: DialectType = None, **kwargs) -> Func:
7021    """
7022    Returns a Func expression.
7023
7024    Examples:
7025        >>> func("abs", 5).sql()
7026        'ABS(5)'
7027
7028        >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql()
7029        'CAST(5 AS DOUBLE)'
7030
7031    Args:
7032        name: the name of the function to build.
7033        args: the args used to instantiate the function of interest.
7034        copy: whether to copy the argument expressions.
7035        dialect: the source dialect.
7036        kwargs: the kwargs used to instantiate the function of interest.
7037
7038    Note:
7039        The arguments `args` and `kwargs` are mutually exclusive.
7040
7041    Returns:
7042        An instance of the function of interest, or an anonymous function, if `name` doesn't
7043        correspond to an existing `sqlglot.expressions.Func` class.
7044    """
7045    if args and kwargs:
7046        raise ValueError("Can't use both args and kwargs to instantiate a function.")
7047
7048    from sqlglot.dialects.dialect import Dialect
7049
7050    dialect = Dialect.get_or_raise(dialect)
7051
7052    converted: t.List[Expression] = [maybe_parse(arg, dialect=dialect, copy=copy) for arg in args]
7053    kwargs = {key: maybe_parse(value, dialect=dialect, copy=copy) for key, value in kwargs.items()}
7054
7055    constructor = dialect.parser_class.FUNCTIONS.get(name.upper())
7056    if constructor:
7057        if converted:
7058            if "dialect" in constructor.__code__.co_varnames:
7059                function = constructor(converted, dialect=dialect)
7060            else:
7061                function = constructor(converted)
7062        elif constructor.__name__ == "from_arg_list":
7063            function = constructor.__self__(**kwargs)  # type: ignore
7064        else:
7065            constructor = FUNCTION_BY_NAME.get(name.upper())
7066            if constructor:
7067                function = constructor(**kwargs)
7068            else:
7069                raise ValueError(
7070                    f"Unable to convert '{name}' into a Func. Either manually construct "
7071                    "the Func expression of interest or parse the function call."
7072                )
7073    else:
7074        kwargs = kwargs or {"expressions": converted}
7075        function = Anonymous(this=name, **kwargs)
7076
7077    for error_message in function.error_messages(converted):
7078        raise ValueError(error_message)
7079
7080    return function
7081
7082
7083def case(
7084    expression: t.Optional[ExpOrStr] = None,
7085    **opts,
7086) -> Case:
7087    """
7088    Initialize a CASE statement.
7089
7090    Example:
7091        case().when("a = 1", "foo").else_("bar")
7092
7093    Args:
7094        expression: Optionally, the input expression (not all dialects support this)
7095        **opts: Extra keyword arguments for parsing `expression`
7096    """
7097    if expression is not None:
7098        this = maybe_parse(expression, **opts)
7099    else:
7100        this = None
7101    return Case(this=this, ifs=[])
7102
7103
7104def cast_unless(
7105    expression: ExpOrStr,
7106    to: DATA_TYPE,
7107    *types: DATA_TYPE,
7108    **opts: t.Any,
7109) -> Expression | Cast:
7110    """
7111    Cast an expression to a data type unless it is a specified type.
7112
7113    Args:
7114        expression: The expression to cast.
7115        to: The data type to cast to.
7116        **types: The types to exclude from casting.
7117        **opts: Extra keyword arguments for parsing `expression`
7118    """
7119    expr = maybe_parse(expression, **opts)
7120    if expr.is_type(*types):
7121        return expr
7122    return cast(expr, to, **opts)
7123
7124
7125def array(
7126    *expressions: ExpOrStr, copy: bool = True, dialect: DialectType = None, **kwargs
7127) -> Array:
7128    """
7129    Returns an array.
7130
7131    Examples:
7132        >>> array(1, 'x').sql()
7133        'ARRAY(1, x)'
7134
7135    Args:
7136        expressions: the expressions to add to the array.
7137        copy: whether to copy the argument expressions.
7138        dialect: the source dialect.
7139        kwargs: the kwargs used to instantiate the function of interest.
7140
7141    Returns:
7142        An array expression.
7143    """
7144    return Array(
7145        expressions=[
7146            maybe_parse(expression, copy=copy, dialect=dialect, **kwargs)
7147            for expression in expressions
7148        ]
7149    )
7150
7151
7152def tuple_(
7153    *expressions: ExpOrStr, copy: bool = True, dialect: DialectType = None, **kwargs
7154) -> Tuple:
7155    """
7156    Returns an tuple.
7157
7158    Examples:
7159        >>> tuple_(1, 'x').sql()
7160        '(1, x)'
7161
7162    Args:
7163        expressions: the expressions to add to the tuple.
7164        copy: whether to copy the argument expressions.
7165        dialect: the source dialect.
7166        kwargs: the kwargs used to instantiate the function of interest.
7167
7168    Returns:
7169        A tuple expression.
7170    """
7171    return Tuple(
7172        expressions=[
7173            maybe_parse(expression, copy=copy, dialect=dialect, **kwargs)
7174            for expression in expressions
7175        ]
7176    )
7177
7178
7179def true() -> Boolean:
7180    """
7181    Returns a true Boolean expression.
7182    """
7183    return Boolean(this=True)
7184
7185
7186def false() -> Boolean:
7187    """
7188    Returns a false Boolean expression.
7189    """
7190    return Boolean(this=False)
7191
7192
7193def null() -> Null:
7194    """
7195    Returns a Null expression.
7196    """
7197    return Null()